### 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)**

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)**

### Download workbook

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

Weighted_average