Weighted Averages

[HW162]

Hello

I’m attempting to analysis survey data using weighted averages but must be making a fundamental mistake. As you can see from the extract below the weighted average should fall somewhere between 60% and 90% yet my result is 57%.

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1												SUM
2	Weight:	0.20	0.18	0.16	0.13	0.11	0.09	0.07	0.04	0.02	0.00	1.00
3	Selection List:	90%	80%	70%	60%	50%	40%	30%	20%	10%	0%	450%
4	Number of Respondents who selected %:	20			4							24
5	%:	83%			17%
6
7	Weighted Average:	57%
Local_Arrays (2)

Cell Formulas
Range	Formula
B2:K2	B2	=B3/$L$3
L2:L4	L2	=SUM(B2:K2)
B5,E5	B5	=B4/$L$4
B7	B7	=SUMPRODUCT($B$5:$K$5,$B$2:$K$2)/SUMPRODUCT(--($B$5:$K$5<>""),$B$2:$K$2)

The weightings used have been arrived at by sum of the selection %s divided by the selection percentages. The weighted average formulae used is =SUMPRODUCT($B$5:$K$5,$B$2:$K$2)/SUMPRODUCT(--($B$5:$K$5<>""),$B$2:$K$2) so to ignore blank cells.

I’d greatly appreciate any help you can give. With thanks and regards.

Chris

 

[StephenCrump]

Welcome to the Forum!

You're overthinking the calculation. If you have 20 @ 90% and 4 at 60%, the weighted average for the 24 is 85%. (As you observe, it will necessarily fall between 90% and 60% depending on the relative weights 20 and 4).

	A	B	C	D	E	F	G	H	I	J	K	L
1												SUM
2
3	Selection List:	90%	80%	70%	60%	50%	40%	30%	20%	10%	0%	4.5
4	Number of Respondents who selected %:	20			4							24
5
6		85%
Sheet1

Cell Formulas
Range	Formula
L3:L4	L3	=SUM(B3:K3)
B6	B6	=SUMPRODUCT(B3:K3,B4:K4)/L4

 

[HW162]

Thank you very much Stephen. Your solution is dead on as was your comment. I have a tendency to overthink calcs. Cheers and best, Chris

 

[StephenCrump]

You're welcome. I'm glad we could help.