Weighted average question

[jnasr]

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.

 

[Harlan Grove]

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)

 

[jnasr]

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%.

 

[joeu2004]

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$100>100),B2:$B$100,C2:$C$100) /
SUMPRODUCT(--(A2:$A$100=A2),--(B2:$B$100>100),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$100>100) 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).

 

[Harlan Grove]

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:B6>100),B2:B6,C2:C6)
/MAX(1,SUMPRODUCT(--(A2:A6=1983),--(B2:B6>100),B2:B6))

 

[jnasr]

That works - thanks! I glanced quickly at the data set and neglected
the other data point for 1984. Believe me, if I couldn't calculate
weighted average by hand, I would have plenty more to worry about.

 

[joeu2004]

Errata....

=IF(A2=A1, "",
SUMPRODUCT(--(A2:$A$100=A2),--(B2:$B$100>100),B2:$B$100,C2:$C$100) /
SUMPRODUCT(--(A2:$A$100=A2),--(B2:$B$100>100),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.

 

[joeu2004]

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(...))