Is there a formula in excel to quickly perform a weighted average on a table of data?
Barry Mongan
Replies (9)
Please login or register to join the discussion.
Yes you can, AW!
In spite of its age, this is a perennial favourite on the site - as the 21k traffic figure suggests. To make life easier for those interested in this subject, Simon Hurst has written a more detailed tutorial:
Use Excel SUM functions to calculate weighted averages
Hope this helps!
John Stokdyk
Technology editor
AccountingWEB.co.uk
Formula differences
Hi Jennifer
By a rather wierd coincidence - given that this post is almost 8 years old - I've just finished writing a follow up article on weighted average calculations...
I haven't investigated your formula in detail, but I'd be inclined to split the formula into its constituent bits and see where the difference originates. As I understand it, Sumproduct() should just mulitply FN294 by FN2, FO294 by FO2 etc. If you use a row to calculate all the 294s by all the 2s, does the sum of this row equal the Sumproduct()?
Regards
Simon Hurst
Weighted Average Formula in Excel
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.
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!
Sumproduct
Jay
You say that sumproduct has to be entered as an array. Yet it gives the same answer whether you do or you don't. Why did you say it?
Cheers Jay
Thanks for that Jay - that works fine. I've never used arrays before but they seem fairly useful.Barry.