Weighted Average Excel Formula

[acool]

Hi Everyone,

I am currently trying to create an excel formula that calculates the weighted average number of Months on Hand based on columns A, B & C. While I know that a sumproduct formula should be used in this instance, I am not sure of how this sumproduct should be used exactly. Any help would be greatly appreciated. Thank You!

 

[StephenCrump]

Here's the idea:

	A	B	C
1	Test	Weight	Mark
2	A	10	40%
3	B	10	20%
4	C	40	90%
5	D	40	80%
6		100
7
8	Average		58%
9	Weighted average		74%
Sheet1

Cell Formulas
Range	Formula
B6	B6	=SUM(B2:B5)
C8	C8	=AVERAGE(C2:C5)
C9	C9	=SUMPRODUCT(B2:B5,C2:C5)/B6

In this example, the scores are higher in the two tests that matter - C and D have the higher weightings. Hence the weighted average is higher than the simple average.

But if you set the weights identically, the weighed average will equal the simple average, 58%.

In your example, you could in theory weight either by total units, or by monthly sales:

	A	B	C	D
1	Total	Sales	Months on hand
2	5,000	700	7.1
3	1,000	75	13.3
4	20	2	10.0
5	30,000	20	1,500.0
6	2,500	100	25.0
7	50,000	2,500	20.0
8
9	Total weighted		520.9
10	Sales weighted		26.1
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=A2/B2
C9	C9	=SUMPRODUCT($C2:$C7,A2:A7)/SUM(A2:A7)
C10	C10	=SUMPRODUCT($C2:$C7,B2:B7)/SUM(B2:B7)

But do you think it's appropriate to be using a weighted average at all in this context? What is the answer telling you, and is it meaningful information?

 

[acool]

Thank You!