If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I calculate a weighted average and exclude the empthy cells
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! |
#2
|
|||
|
|||
How do I calculate a weighted average and exclude the empthy cells
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! |
#3
|
|||
|
|||
How do I calculate a weighted average and exclude the empthycells
On May 27, 5:39*pm, aganoe 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! |
Thread Tools | |
Display Modes | |
|
|