Need a formula for a weighted average that ignores blanks.

[PaddyP]

Hi All,

Please see attached. Column R is what I want to be returned in Column P. I am just struggling with how to make the formula.

Note Column M will never be blank but N and/or O could be.

Thanks

[Sam Capricci]

try this...
=IF(AND(M8<>"",N8<>"",O8<>""),(M8*$M$6)+(N8*$N$6)+(O8*$O$6),IF(AND(N8="",O8<>""),(M8*$M$6)+(O8*$O$6),IF(AND(N8<>"",O8=""),(M8*$M$6)+(N8*$N$6),M8)))

this is on the assumptions that, a) M will never be blank and b) if only M is populated you don't need that multiplied by the weight in M6.

[Sam Capricci]

Out of curiosity, why are you multiplying the numbers in each row by their weights THEN dividing them by the addition of the weights in their respective columns?
and BTW, this is a shorter version of what I gave you earlier that will return the same values but those values simply match what is in col P, not R...
=M8*$M$6+N8*$N$6+O8*$O$6

[TMS]

I can give you several formulae that return the values you have, but say you don't want.

TMS 1

Please Login or Register  to view this content.

TMS 2

Please Login or Register  to view this content.

TMS 3

Please Login or Register  to view this content.

I can also give you more consistent versions of the formulae that you say gives the results you want:

TMS 1

Please Login or Register  to view this content.

TMS 2

Please Login or Register  to view this content.

TMS 3

Please Login or Register  to view this content.

TMS 4

Please Login or Register  to view this content.

TMS 5

Please Login or Register  to view this content.

That said, I do not understand the logic that gives you the answers that you do want

[PaddyP]

Thanks for the responses but these formulas wont work.

The logic is I want to take a weighted average using A, B & C but there will be instances where B or C may not have a result. In those instances I want to take a new weighted average.

Looking at Option # 2 for example. Model A predicts 67 and Model C predicts 75. Since Model B did not make a prediction the new weight for A would be 60/75 and C becomes 15/75. Which is 80% and 20% (The same ratio of A to C we wanted in the first place)

[MrShorty]

If I am understanding correctly, you are wanting to use a sum(xi*wi)/sum(wi) [where xi are the values and wi are the weights] to calculate your weighted averages, is that correct?

My implementation of this kind of weigted average would be:

1) sum(xi*wi) is a simple SUMPRODUCT() -- SUMPRODUCT(M8:O8,$M$6:$O$6). Note the mix of relative and absolute references.
2) sum(wi) would be a simple SUM($M$6:$O$6) if there were no conditions, but you have indicated you only want to sum the weights where there is a value present in row 8. I use a SUMIFS() to perform the conditional sum SUMIFS($M$6:$O$6,M8:O8,">0") where I am assuming that the cells in M8:O8 can never be a value <=0. If my assumption is incorrect, then we'll need to come up with a different criteria.

Is that what you are trying to do?

[PaddyP]

Winner winner!

Fantastic formula this is exactly what I was looking for!

You are correct to assume that there will not be values <0

Thank you very much!

[dhaka007]

Yes.
That's a good solution. I try and success.