Re-distribution of weights - formula help needed ?

Before we can build a formula, we probably need to know what kind of algorithm or calculation scheme you want to use.

As a guess, would it be suitable to simply renormalize the weightage? I notice that renormalizing the weightage [F4/SUM(F$4:F$7)] give 21.2% x3 and 36.4%. That's not exactly the same as what you suggest in column J, but it's close. Would that be an adequate calculation scheme -- to renormalize the weightage?

Maybe something like

1) IF() function to detect the N/A text string and return 0 if N/A or the value in F if not N/A. IF(H3="N/A",0,F3)
2) The denominator is simply the sum of F wherever H is not N/A, so a simple SUMIFS() function SUMIFS(F$3:F$7,H$3:H$6,"<>N/A")
3) Divide (1) by (2) =IF(...)/SUMIFS(...).
4) Note the mix of relative and absolute references for easy copying and copy/paste/fill down

Hi all.
Per dimitrz's expected results, the following - in F3, copied down - splits the "unused" N/A portion(s) equally between the active criteria:

Formula:

Please Login or Register  to view this content.

Per MrShorty's post #4, with adjusted ranges:

Formula:

Please Login or Register  to view this content.

IMO, MrShorty 's approach yields a more useful distribution because the active weights are adjusted proportionally, thus retaining their original ratios.

Many Thanks Mr Shorty & Leelnich
you guys have no idea how many days and hours I had spent trying to get this right , Thanks a lot much appreciated .
Especially to Leelnich for clarifying Mr Shorty's formula practically.


I have one final query

How to I avoid the Sum error in column J because of empty space due to N/A in Column H

Attachment 841533

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

You're welcome. If appropriate, please mark your thread SOLVED. - Lee

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)