How do I calculate a weighted average and exclude the empthy cells

[aganoe]

I am looking to complete my formula for calculated weighted averages.
Here is what I have:
A B C D
1 weight 20% 30% 50% Avg results
2 results 1 100% 100% 90% 95%
3 results 2 100% 90% 47%

The formula I have thus far is for D2: =average(a2*$a$1)+(b2*$b$1)+(c2*$c$1)
which works, however, when I apply the same formula to row 3, it creates an
incorrect average as it calculates the empty cell. Can someone please help
me resolve my delemna and provide me with possible solution? Thank you!

 

[Sheeloo]

1. How will you do it on paper?

2. You have the parenthesis wrong...
=average(a2*$a$1)+(b2*$b$1)+(c2*$c$1)
Above calculates average(a2*$a$1) then adds (b2*$b$1) and then adds (c2*$c$1)
What you intended was perhaps
=average(a2*$a$1+b2*$b$1+c2*$c$1)

3. Also, in the above, Excel evaluates a2*$a$1+b2*$b$1+c2*$c$1 to a
number.... so the formula calculates average for one number, which is the
same number...

Use
=(A2*$A$1)+(B2*$B$1)+(C2*$C$1)

Since your weights sum up to 100% you don't have to divide with the sum of
weights.

4. If you want to exclude empty columns from calculation, then you will have
to redistribute the weights...

5. Weighted Average formulas is SUM(a1*w1+a2*w2+a3*w3)/sum(w1+w2+w3)

 

[Pieter Vandenberg]

Hello:

Why is the average incorrect? What number do you think it should be?

Given that you weight the first 20% and the second 30% the average is
47. What your problem is that you need to
define how you want the weights to change when there is a number
missing. To get a "correct" weighted average
the weights need to always sum to 100%.

BTW you don't need to use "average" in the formula. It will provide
the same answer without it.

Pieter Vandenberg