Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted average question
Hi,
I am trying to compute the weighted average of a data set, conditional on two criteria. I know how to do it for one, but am struggling to trigger the second. Any help would be appreciated. A simplified data set is: A B C 1 Year Size Return 2 1983 100 20% 3 1983 75 10% 4 1984 200 22.5% 5 1984 150 15% 6 1985 300 10% I would like to be able to create a formula that would allow me to calculate the weighted average of each year's returns if the size is greater than 100. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted average question
jnasr wrote...
.... A B C 1 Year Size Return 2 1983 100 20% 3 1983 75 10% 4 1984 200 22.5% 5 1984 150 15% 6 1985 300 10% I would like to be able to create a formula that would allow me to calculate the weighted average of each year's returns if the size is greater than 100. For 1983, two possibilities: =SUMPRODUCT(--(A2:A6=1983),B2:B6,C2:C6)/SUMPRODUCT(--(A2:A6=1983),B2:B6) =SUMPRODUCT(--(A2:A6=1983),B2:B6/SUMIF(A2:A6,1983,B2:B6),C2:C6) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted average question
Thanks, but this would only return the weighted average of ALL return
data for 1983. I am trying to screen for both a specific year and size over 100. So the formula for 1983 should return 0, 1984 should return 22.5% and 1985 should return 10%. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted average question
jnasr wrote:
So the formula for 1983 should return 0, 1984 should return 22.5% How do you figure that!? I compute 19.3% approximately, namely: (200*22.5% + 150*15%) / (200 + 150). jnasr wrote originally: A B C 1 Year Size Return 2 1983 100 20% 3 1983 75 10% 4 1984 200 22.5% 5 1984 150 15% 6 1985 300 10% I would like to be able to create a formula that would allow me to calculate the weighted average of each year's returns if the size is greater than 100. Does the following satisfy your needs. Starting in D2 (and copy down), put: =IF(A2=A1, "", SUMPRODUCT(--(A2:$A$100=A2),--(B2:$B$100100),B2:$B$100,C2:$C$100) / SUMPRODUCT(--(A2:$A$100=A2),--(B2:$B$100100),B2:$B$100) This puts a number into the D cell that corresponds to the first row of a year; e.g. D2, D4 and D6. It puts a blank into other D cells; e.g. D3 and D5. The first condition (A2:$A$100=A2) is true only for the first row of a year. The second condition (B2:$B$100100) is the size constraint that you specify. In the numerator, the product of the third and last parameters computes the total number of returns (subject to the other conditions). In the denominator, the last parameter computes the total "size" values (subject to the other conditions). This presumes that "size" is a quantity count, not a category (e.g. size in millimeters). |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted average question
jnasr wrote...
Thanks, but this would only return the weighted average of ALL return data for 1983. I am trying to screen for both a specific year and size over 100. So the formula for 1983 should return 0, 1984 should return 22.5% and 1985 should return 10%. Sorry, missed the size over 100 bit. =SUMPRODUCT(--(A2:A6=1983),--(B2:B6100),B2:B6,C2:C6) /MAX(1,SUMPRODUCT(--(A2:A6=1983),--(B2:B6100),B2:B6)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted average question
Errata....
I wrote: =IF(A2=A1, "", SUMPRODUCT(--(A2:$A$100=A2),--(B2:$B$100100),B2:$B$100,C2:$C$100) / SUMPRODUCT(--(A2:$A$100=A2),--(B2:$B$100100),B2:$B$100) [....] The first condition (A2:$A$100=A2) is true only for the first row of a year. The formula works just fine. But this part of the explanation is faulty due to a last-minute edit. The first condition limits the SUMPRODUCTs to only those cells that correspond to the same date. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted average question
jnasr wrote:
That works - thanks! You're welcome. Please note Harlan's embellishment in the denominator, which guards against division by zero in a year which has no "size" greater than 100, namely: sumproduct(...) / max(1, sumproduct(...)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weighted Average - Copy Function | Excel Worksheet Functions | |||
Non zero weighted average | Excel Worksheet Functions | |||
Can you calculate "weighted average cost of capital? | Excel Discussion (Misc queries) | |||
Average If Question | Excel Discussion (Misc queries) | |||
Average If Question | Excel Discussion (Misc queries) |