Weighted Average If statement

[legend X]

Hi, everyone. Can someone please help me with a weighted average formula. There are 3 sections column B:D is 0.20% overall, column E:G is 0.40% overall and column H:J is 0.40% overall. I want a formula that will look for the company name in column A and return an overall weighted average score for COKE, PEPSI, MIST & DEW.

 

[Hui]

Legend X

Firstly, Welcome to the Chandoo.org Forums

Using Dummy Data because you didn't attach a sample file
I would do as below


First, Spread the Weightings evenly to each component in the different colors

Then use a formula in B28: =SUMPRODUCT(($B$3:$J$26*$B$1:$J$1)*($A$3:$A$26=A28))/SUMPRODUCT(--($A$3:$A$26=A28))

Copy it down

or see the attached file:

 

[legend X]

You sir are a genius. Appreciate your help. Thanks so much!

 

[legend X]

Hui, I'm running into a little problem here.. if I have blanks in the data range the formula does not ignore it and ends up lowering the overall weighted average. I have done the weighted average manually for pepsi which is highlighted in green. Can you make a change in the formula so that it ignores the blank cells and both highlighted cells end up having the same average. Many thanks for your help.

 

[Hui]

Your math is, although at face value correct, wrong
What you are doing is averaging the range, but because each column has different number of Non-blank rows or columns, which is incorrect. You haven't taken the weighting in of each Column

My formula is also wrong, for similar reasons

New Solution

B28:
=SUMPRODUCT(($B$3:$J$26)*($B$1:$J$1)*($A$3:$A$26=A28)) / SUMPRODUCT(($A$3:$A$26=$A28)*($B$3:$J$26<>0)*($B$1:$J$1))
Then copy that down

 

[legend X]

Thank you Hui for all your help! Greatly appreciate it.