Weighted average question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jnasr - 06 Nov 2006 18:38 GMT

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.

Reply to this Message

Harlan Grove - 06 Nov 2006 18:54 GMT

jnasr wrote...
...

> A B C
>1 Year Size Return
[quoted text clipped - 7 lines]
>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)

Reply to this Message

jnasr - 06 Nov 2006 18:59 GMT

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

Reply to this Message

joeu2004@hotmail.com - 06 Nov 2006 20:14 GMT

> 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
[quoted text clipped - 7 lines]
> 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).

Reply to this Message

jnasr - 06 Nov 2006 20:23 GMT

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.

> > So the formula for 1983 should return 0, 1984 should return 22.5%
>
[quoted text clipped - 34 lines]
> This presumes that "size" is a quantity count, not a category (e.g.
> size in millimeters).

Reply to this Message

joeu2004@hotmail.com - 06 Nov 2006 20:33 GMT

> 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 to this Message

joeu2004@hotmail.com - 06 Nov 2006 20:25 GMT

Errata....

I wrote:

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

Reply to this Message

Harlan Grove - 06 Nov 2006 20:21 GMT

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

Reply to this Message