Weighted Average Formula in Excel

[jsb1]

Needing to reduce the size of the formula below used to calculate the weighted average, I've tried using your suggestion of {=SUMPRODUCT(A1:A10*B1:B10)/SUM(B1:B10)}. However, using this formula gives me different value than the longer formula below:


=(((FN294+FO294)*0.5*(FO$2-FN$2))+((FO294+FP294)*0.5*(FP$2-FO$2))+((FP294+FQ294)*0.5*(FQ$2-FP$2))+((FQ294+FR294)*0.5*(FR$2-FQ$2))+((FR294+FS294)*0.5*(FS$2-FR$2))+((FS294+FT294)*0.5*(FT$2-FS$2))+((FT294+FU294)*0.5*(FU$2-FT$2))+((FU294+FV294)*0.5*(FV$2-FU$2))+((FV294+FW294)*0.5*(FW$2-FV$2))+((FW294+FX294)*0.5*(FX$2-FW$2))+((FX294+FY294)*0.5*(FY$2-FX$2))+((FY294+FZ294)*0.5*(FZ$2-FY$2))+((FZ294+GA294)*0.5*(GA$2-FZ$2)))/(GA$2-FN$2)

Using the longer formula above, the result value is 1.60% but using the shorter formula above =SUMPRODUCT(FN294:GA294*FN2:GA2)/SUM(FN2:GA2), the result value is 1.77%. Additionally, the shorter the range, the higher the delta is when comparing the resullts of these two formulas. Row D represents weekly dates and row 294 represents percentages.

Is the shorter formula (sumproduct) correct or is my longer formula not calculating the weighted average correctly? Any idea why there is a discrepency? Thanks!

 

[cornflakegirl]

Welcome to the board.

I'm not really sure what your formula is doing. It's not something that I recognise as being a weighted average. However, if it is what you want, you can replicate it as follows:

=SUMPRODUCT((A1:A9+A2:A10)*0.5*(B2:B10-B1:B9))/(B10-B1)