Calculate Weighted Mean Given a Range of Values

[wsnyder]

Hi,

How can I calculate a weighted mean, give a range of values?
Let me explain

I have a range of values, 11% - 20%. 17% of all scores fell within that range

Bucket	Low	High	Score
11 - 20 pcnt	0.11	0.2	17%
21 - 30 pcnt	0.21	0.3	17%
31 - 40 pcnt	0.31	0.4	17%
41 - 50 pcnt	0.41	0.5	33%
51 - 60 pcnt	0.51	0.6	17%
			100%

I tried Sumproduct/Sum on both ends of the spectrum
On Low, that is giving me 32.66%
On High that is giving me 41.66%

The final mean should fall closer but not over 41.66% since 67% of scores are 30 and higher.

Thanks,
-w

Group	Formula
Low	=SUMPRODUCT(B2:B6,D2:D6)/SUM(D2:D6)
High	=SUMPRODUCT(C2:C6,D2:D6)/SUM(D2:D6)

 

[tjdrake]

I used a helper field to calculate the average of the high & low

Weighted Average.xlsx
	A	B	C	D	E
1	Bucket	Low	High	weight	Helper
2	11 - 20 pcnt	11%	20%	16.67%	15.50%
3	21 - 30 pcnt	21%	30%	16.67%	25.50%
4	31 - 40 pcnt	31%	40%	16.67%	35.50%
5	41 - 50 pcnt	41%	50%	33.33%	45.50%
6	51 - 60 pcnt	51%	60%	16.67%	55.50%
7
8	Weighted Avg:	37.17%
9	=SUMPRODUCT(Table3[Helper],Table3[weight])/SUM(Table3[weight])
Sheet4

Cell Formulas
Range	Formula
E2:E6	E2	=AVERAGE(Table3[@[Low]:[High]])
B8	B8	=SUMPRODUCT(Table3[Helper],Table3[weight])/SUM(Table3[weight])

 

[wsnyder]

Thanks tjdrake,

That gives me a weighted avg of 37.16. Definitely better than simple average of 35.5. Still the data from the person that sent to me shows a mean of 39.17.

Thanks,
w