Prev: sales tax chart
Next: Formula to display nearest following Thursday in mm/dd/yyyy format
From: aganoe on 27 May 2010 20:39
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!

From: Sheeloo on 27 May 2010 23:19
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)

"aganoe" wrote:

> 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!
>
From: Pieter Vandenberg on 27 May 2010 23:34
On May 27, 5:39 pm, aganoe <aga...(a)discussions.microsoft.com> wrote:

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

> 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!

 | 
Pages: 1
Prev: sales tax chart
Next: Formula to display nearest following Thursday in mm/dd/yyyy format