Excel - weighted average formula

Excel - weighted average formula

Didn't find your answer?

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.

John Stokdyk, AccountingWEB head of insight
By John Stokdyk
02nd Jun 2009 09:47

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

Thanks (0)
avatar
By User deleted
21st May 2009 09:39

Can I nominate this for
the ancient thread resurrection award

:0

Thanks (0)
Simon Hurst
By Simon Hurst
20th May 2009 21:24

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

Thanks (0)
avatar
By jjssb
20th May 2009 16:12

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!

Thanks (0)
aw_logo_2019
By Accounting WEB
08th Aug 2001 21:05

My Mistake
Ian,

Yes you are right. It works fine without entering as an array. I thought I read somewhere that it was designed to work with arrays especially if you have non-contiguous cells. But for our example it does the job so just use it.

Best regards,

Jay Tanna

Thanks (0)
avatar
By Wingy
08th Aug 2001 16:24

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?

Thanks (0)
aw_logo_2019
By Accounting WEB
08th Aug 2001 22:24

The syntax for Sumproduct is ....

Ian and all,

The syntax for sumproduct is quite simple: It is like this:

=SUMPRODUCT(A1:A6,B1:B6)

Where A1:A6 are qtys
B1:B6 could be price.

You still divide by Sum(B1:B6) to get weighted average;

So this one seems to be easier.

Thanks for pointing this out.

Regards,

Jay Tanna

Thanks (0)
aw_logo_2019
By Accounting WEB
03rd Aug 2001 23:40

Weighted Average Formula in Excel
Hi Barry,

Excel doesn't give you the direct formula for weighted average but there is a workaround which achieves the same result. For example, you have quantities in {A1:A10} and cost price in B1:B10. You want to find the total cost price of the stock and also the weighted average price of the stock item. You do this:

In an empty cell enter the following array formula:

{=SUM(A1:A10*B1:B10)/SUM(B1:B10)}

This is an array formula means you don't enter the curly brackets but when you have entered your formula, you press CNTRL+SHIFT+ENTER
all three keys together. This formula will give you the average cost of an item.

To find the total cost you simply enter (array formula again):

{=SUM(A1:A10*B1:B10)}

Hope your get the idea.

Excel also gives you another function called SUMPRODUCT which also needs to be entered as an array: In our example you would enter:

{=SUMPRODUCT(A1:B10*B1:B10)}

TO GET WEIGHTED AVERAGE: you do:

{=sumproduct(A1:A10*B1:B10)/SUM(B1:B10)}

You get the same result either way.

Hope this helps.

Jay Tanna

Thanks (0)
avatar
By bmongan
06th Aug 2001 12:21

Cheers Jay
Thanks for that Jay - that works fine. I've never used arrays before but they seem fairly useful.Barry.

Thanks (0)