Skip to main content
weighted average explained

Excel formulas: how to calculate weighted average

Definition

An average can be defined as the result obtained by adding several quantities together and then dividing this total by the number of quantities.

The difference with a weighted average is that we will give each number a weight. This number will represesent the importace of the number within our data set.

Click here for the wikipedia explonation.

Why would we use this?

Another good example to explain this concept is the following joke from Tim Ferris:

“The extremes inform the mean, but not vice versa.

That average can be deceptive or even meaningless.. Here’s a statistician joke for your next hot date:

Person A: What happens when Bill Gates walks into a bar of 55 people?
Person B: I don’t know. What?
Person A: The “average” net worth jumps to more than a billion dollars! (Bill Gates’s estimated net worth as of March 2012 was $61 billion.)

The joke makes an important point: sometimes it pays to model the outliers, not flatten them into averages.” – Tim Ferris, Tim.Blog

Calculating normal average

All scores in de table are between 0 and 20.

Let’s start of by calculating the normal averages. An average is the sum of a list of numbers divided by the quantity of numbers in the list. Use following formula: =AVERAGE(B14:D14)
We will get the following result:

Tip: A ‘$’ will lock down the reference to an absolute one versus a relative one. You can use this for a column, row or both.

But not all the results are equally important. As an example: an exam is more important than regular homework. So let me show you a better method.

Calculating weighted average

Weighted Average with SUM

We calculate this with the following formula:

=(result 1 x weight +result 2 x weight + result 3 x weight) / (weight 1 + weight 2 + weight 3).

For our first student the result is 14,8/20 .

=SUM(B14*$B$29,C14*$C$29,D14*$D$29)/SUM($B$29:$D$29)

weighted average explained

This is difficult to do when you have a lot of data. In that case, SUMPRODUCT is a better alternative.

Weighted Average with SUMPRODUCT

Use the following formula:

=SUMPRODUCT(list with grades, range of list with weights)/ SUM(range of list with weights)

=SUMPRODUCT(B15:D15, $B$29:$D$29) / SUM($B$29:$D$29)

weighted average witg sumproduct

 

Download workbook

Feel free to download my excel workbook and try this example yourself. Click on the link to start the download.
Weighted_average

 

Leave a Reply

%d bloggers like this: