Weighted Average formula for Statistics

Hello Laura,

Thank you for your question. To calculate weighted average, Excel uses a combination of SUMPRODUCT and SUM. The structure of the formula is as follows:

=SUMPRODUCT(Range_1, Range_2)/SUM(Range_1)

This formula assumes that Range_1 is the column which contains the numbers of people and Range_2 is the column which contains the percentages (scores).


The formula multiplies the number of people with the score for each row in the range and then totals all these results. Then it divides this result by the total number of people, to give the weighted average.


Rows with more people will carry more weight.


I hope this helps.


Kind regards
Marius Barnard
Excel Trainer