Weighted Average Formula In Excel (Examples)

by

You might come across some data distributions where different quantities carry different levels of importance (or weightage).

A standard average is not really a good representation of such distributions, since it does not take into account these ‘weights’.

A better option would be to use the weighted average to represent the data.

In this tutorial we will explain what weighted average is, how it differs from the standard average, and two ways to find the weighted average of a variable in Excel:

  • Using the general formula
  • Using the SUMPRODUCT function

What is Weighted Average and How is it Different from a Standard Average?

The weighted average is a special kind of arithmetic mean that gives different weights to different quantities.

For example, in a school examination, some tests might carry a higher weightage than others.

Let’s consider a hypothetical school exam, with the following weights given to each test:

In the above example, a standard average would simply sum up the individual test scores and divide it by the number of tests.

In other words, the standard average would be (40+80+80) / 3 = 66.7.  

However, this result is not an accurate representation of the student’s performance, because the student did better in the mid-term exam, which was more important than the unit test.

But his/her bad performance in the unit test brought his score down lower to what it should be.

If we calculate the weighted average, however, we could take into account the weights of individual tests.

The weighted average is calculated by multiplying each quantity (each test score in this case) with its respective weight, adding up these products, and dividing the sum by the sum of weights.

In other words, if x1, x2, …, xn are individual values of a variable and w1, w2, …., wn are their corresponding weights, then the weighted average of this variable can be calculated as follows:

Weighted average = (x1w1+x2w2+…+xnwn)/(w1+w2+..wn)

In our example, the weighted average score of the student will be 

(40 * 0.1) + (80 * 0.3) + (80 * 0.6) = 76

As you can see the standard average and weighted average are two different values.

However, the weighted average paints a much better picture of the student’s performance because it also considers the importance of each test score.

Two Ways to Calcuate Weighted Average In Excel

We saw from the above example that the formula to calculate the weighted average of a set (x1, x2, …, xn) with weights (w1, w2, …., wn) is as follows:

Weighted average=(x1w1+x2w2+…+xnwn)/(w1+w2+..wn)

Now let us see two ways to calculate the weighted average in Excel.

To demonstrate the methods, we will use the following dataset:

Using the General Formula to Calculate Weighted Average in Excel

The first method uses the same general formula that we used in the previous section to find the weighted average.

This formula takes the product of individual values with corresponding weights, sums them up and divides this result by the sum of weights.

In other words, we can use the following formula (for our sample data):

=SUM(B2*C2,B3*C3,B4*C4,B5*C5,B6*C6)/SUM(C2:C6)

The result we get is as follows:

The SUM function in Excel simply adds up the values provided as its parameters.

Note that this formula essentially performs the same calculation that we had described in the previous section.

The formula is very simple and easy to understand. However, it’s not very practical when you have a large number of values to average.

A better way would be to use an array formula inside your SUM function. To do this, you can use a range of cells inside the SUM function instead of entering individual cell references, as follows:

=SUM(B2:B6*C2:C6)/SUM(C2:C6)

Notice that entering the above formula and pressing the return key will give you an error. 

You need to convert it to an array formula by pressing the CTRL+Shift+Enter key after entering the formula.

Doing this will insert curly braces around the formula, showing that it is now an array formula.

You should now get the correct result, without having to enter each cell reference separately.

Note: We are not using the Excel AVERAGE function here because that would only return the standard average value.

Using the SUMPRODUCT Function to Calculate Weighted Average in Excel

The formula to compute the weighted average essentially involves multiplying pairs of quantities and summing them up, or finding the ‘sum product’.

The SUMPRODUCT function is an excellent means to get this done quickly.

The SUMPRODUCT function can be used to multiply individual values with their respective weights as follows:

=SUMPRODUCT(values_range, weights_range) / SUM(weights_range)

Let us apply this function to our sample problem, where we want to compute the weighted average score of a given student.

The formula, in this case, can be written as follows:

=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

The SUMPRODUCT function is going to multiply each value in the first parameter’s range with its corresponding value in the second parameter’s range.

After this, it will sum up all the products and return the sum computed.

Here’s the result it returns:

Notice we get the same result with both methods.

Calculating Weighted Moving Average (WMA) in Excel

One commonly used weighted average variation in the real world is the Weighted Moving Average (WMA).

While the concept of WMA is the same as a regular weighted average, the difference is that it changes with time.

Let me explain with an example.

Below I have a dataset where I have the rainfall value for some given days, and I want to calculate the weighted moving average of the past three days.

WMA Data

Also, in this case, the weightage is:

  • 50% weightage to value 1 day before
  • 30% weightage to value 2 days before
  • 20% weightage to value 3 days before

Below is the formula that will give us the weighted moving average in this example:

=B4*50%+B3*30%+B2*20%

You can copy and paste this formula for all the cells in column C.

Formula for Weighted Moving Average

In this tutorial, we discussed the weighted average and explained how it is different from the standard average.

We also showed you two ways to find the weighted average in Excel.

The first method uses the regular SUM function with an array formula, while the second method uses the SUMPRODUCT function, in combination with the SUM function.

And then we also showed you how to calculate Weighted Moving Average in Excel.

We hope you found the tutorial useful and easy to follow.

Other Excel tutorials you may also find useful:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment