Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

weighted average formula statist

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Weighted Average formula for Statistics

Weighted Average formula for Statistics

ResolvedVersion 2010

Laura has attended:
Excel Intermediate course

Weighted Average formula for Statistics

How can I plot a weighted average for percentages against number of people who did the survey.

RE: 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

Wed 13 Jun 2018: Automatically marked as resolved.

Excel tip:

Apply Autosum with keyboard shortcut

If you press Alt and = at the same time, it applies autosum.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.