Weighted score distribution with fixed maximum

Probably more of a maths than Excel question but going a little round in circles.

Boiling the problem down a bit. Imagine a range of binary values (1 & 0, true & false, yes & no...). I need to give each value a score. If the value is (for example) false the score is always zero. If true the scores vary depending on weights and a total potential score.

Ok, no problem yet.

Now, some of these values could also be "n/a". In this case there is no score but the max potential score for this value needs to be split amongst the other values proportional to their weight so the max score of all the values wich are not "n/a" remains the same.

Hope that is enough explanation and someone has a clue to solve the puzzle.
TIA

Hi,

I'm struggling to visualise this.

If the values are all binary how can a weighting enter the equation? I can see how if values were say 2,4,6 etc. with a weighting to each respectively of 10,11,12 that you could work out a total weighted score ignoring n/a or whatever, but since the non zero score is by definition always 1, I can't see this.

Would you upload an example table of your data and show whatever results you expect to see and explain how you get from one to the other.

Working from my phone at the moment so limited to words. Let me try...

The binary values are just switches. The weights are external. So, if A1:A4 can be either pass or fail and A5 can be pass, fail or n/a. A pass in all scores 100 points. However, due to the 'external' weighting a pass in A1 scores 10, in A2:A3 scores 20 each and in A4:A5 scores 25 each.

This would be simple enough but if A5 is n/a then the potential 25 points from that value needs to be spread over the other values proportionally. So the total of all the passes (and one n/a) remains the same.

Huge thanks daffodil, I'll download and try it in the morning.

Just one thing though... "+ (25/4) points", isnt that ignoring the external score? If so the low score pass has greater proportional value which is what I need to avoid.

You can't spread the n/a values out proportionally like you require, and have the low score not increase proportionally.

You said you wanted the values split up for the n/a.

If you passed the first three, failed the 4th, and skipped the last your scores would be: 16.25, 26.25, 26.25, 0 = 68.75


Did you mean you want to spread the points out disproportionately? Do you want the 10% of 25 points from the n/a to hit 1 instead, and so on and so forth?

I'm, ya, I guess. Proportional to the remaining external weights.