Weighted average formula

Hi Jennifer

Thanks for getting in touch. As you probably know, a regular AVERAGE function will assume all your inputs are of equal importance. To get a weighted average, you'll need to use the SUMPRODUCT function.

The SUMPRODUCT function works by multiplying columns together and adding the results.

If you then divide the result of SUMPRODUCT by the SUM of what you wanted to average, this will give you your weighted average.

So a full formula could look like this

=SUMPRODUCT(Values1,Values2)/SUM(Values1)

I've attached a workbook to give you a hand.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector