Weighted Calculations When Missing Data Points

[bchilme]

Hey all thanks for looking.

So i am trying to compute weighted calculations when some of the data is missing.

Under normal circumstances with no missing data the weights would be as follows:

Please Login or Register  to view this content.

But my issue would be if some of the A-E criteria are missing i would still want the weights to add up to one. So in the below example A is missing which was weighted at (0.25). So to divy that .25 up among the remaining data i would take the weights of the remaining data and divide each of those by the sum of the 4...multiply that by the .25 and then add it back in to each weight. So for example...

Sum of Remainder = .3 +.2 +.2 +.05 = .75

For B = .3 / .75 * .25 = .1

So the new weight for B would be .3 + .1 = .4

And so on for each weight as seen below.

Please Login or Register  to view this content.

There could be 1 data point might be missing or all 5 might have an unknown so there are a lot of different scenarios.

Short of making a helper table with all the different scenarios and making some sort of lookup equation based on the unknown data points then finding the proper weights with that...can anyone think of an easier way to set this up?

Any help would be appreciated.

Thanks all!

[Moo the Dog]

	A	B	C	D	E	F
1	Actual		.3000	.2000	.2000	.0500
2	Adjusted	Unknown	.4000	.2667	.2667	.0667

The formula used in cell B2, then copied through F2 is:

Please Login or Register  to view this content.

So you only need one extra row... hope that works for you?

- Moo

[bchilme]

Hi Moo thanks for the reply!

So i should have expanded on this part of my question. I didnt explain this correct at all. My main question is to make a final calculation based on these different scenarios of unknown data points.

Please Login or Register  to view this content.

Basically with there being from 0-5 data points missing on any given row of data there are lots of possibilities for which weights will be used in a final calculation.

For example..

Listed in row one is the Data.
Row 2 are the weights.
Row 3 would be the calculations.

Please Login or Register  to view this content.

Based on what is missing i would need the calculation to know that in the above example... Since Data point for A is missing it would need to find these weights.

Or looking at the below example:

Please Login or Register  to view this content.

Data point B is missing so the calculation would have to look for the weights when the data point for B is Unknown.

These would be just the first few possibilities of weights. So when there could be 0-5 data points missing there could be like 30 different scenarios....

Please Login or Register  to view this content.

So the only way i could think of making a final calculation following the above is to make some lookup equation to find which data points would be missing out of the 5 and then go to a helper table find the matching weights and use those. So the above weights table.... if C is missing the lookup equation would go to the table and say of since only data point C is missing...use the weights in row 4.

Does that make sense?


Thanks for looking!