Calculating weighted average is a more advanced way of averaging numbers where different weights are given to different numbers unlike equal/uniform weights given in the case of the normal average. In many cases while calculating the average, there is a need for giving importance to one of the factors over the other, this is where weighted average becomes useful. Let’s understand what is a weighted average in more detail and how is it different from a normal average before we get into the details of how to calculate in Excel.

What is “Weighted Average”?

The dictionary definition of weighted average is as follows:

“An average resulting from the multiplication of each component by a factor reflecting its importance.”

To make the understanding simple, I’ll take a basic example and explain what is weighted average and how it is different from normal average. Let’s say, you purchased a laptop worth $400 and 3 desktops worth $100 each. If you were asked to calculate the average price of an item from your purchase, if you just take an average of $400 and $100:

($400+$100)/2=$250

The above calculation would not be correct as there are 3 desktops and just 1 laptop. Hence, to account for the quantities of items purchased, we would add the price of 3 desktops and 1 laptop then divide by the total items i.e. 4 as follows:

(1x$400+3x$100)/4=$175

In the above example, each component was weighted with the corresponding quantities, hence, this is a weighted average and this gives a better result as compared to a normal average.

Mainly, there are two types of weighted averages:

  1. The Weights add up to 100% or 1
  2. The Weights don’t add up to 100% or 1

Now, let’s take a few examples and see how weighted average can be calculated in Excel for both cases mentioned above.

Download the below Excel file with the basic data for reference to follow along with the steps mentioned below.

Download Excel file here.

Example 1: Calculating the weighted average when the weights add up to 100%

  1. Go to the Example 1 sheet in the downloaded workbook. The data available there will be as follows:Weighted Average in Excel 1
  2. In this, the given weights add up to 100%. We’ll use the SUMPRODUCT function to calculate the weighted average
  3. The syntax of SUMPRODUCT function is as follows:

SUMPRODUCT(array1, [array2], [array3],….)

a. Array1 The first array argument whose components you want to multiply and then add

b. Array2, array3,… Array arguments 2 to 255 whose components you want to multiply and then add

  1. The formula first multiplies the respective elements of the array 1 with those of array 2 and so on. Then takes the Sum of all the results of the multiplication
  2. So, our formula will be: SUMPRODUCT($B$2:$B$6,$C$2:$C$6)Weighted Average in Excel 2
  3. This calculates the weighted average as 72
  4. To understand how this works in more detail, I’ve tried to break down the steps performed by SUMPRODUCT. Take a look at the below screenshot, but please note that you do not need to perform the below steps when calculating a weighted average.Weighted Average in Excel 3
  5. As observed in the above screenshot, first the corresponding cells of the array are multiplied, i.e. B2 & C2, B3 & C3 and so on…. Then, the results are added together as shown in cell E7.

The same concept can be extended to the other type of averaging where you will have to divide by the Sum of the weights as the weight don’t add up to 100% or 1.

Example 2: Calculating the weighted average when the weights don’t add up to 100% or 1

  1. Go to the Example 2 sheet in the downloaded workbook. The data available there will be as follows:Weighted Average in Excel 4
  2. In this, the given weights don’t add up to 100%/1. We’ll use the SUMPRODUCT function along with SUM to calculate the weighted average
  3. The syntax of SUMPRODUCT function is as follows:

SUMPRODUCT(Array1, [Array2], [Array3],….)

a. Array1 The first array argument whose components you want to multiply and then add

b. Array2, Array3,… Array arguments 2 to 255 whose components you want to multiply and then add

  1. The syntax of SUM  function is as follows:

SUM(array)

a. Array will be list of all the number to add up

  1. To calculate the weighted average for the above example, we use the formula: SUMPRODUCT($B$2:$B$6,$C$2:$C$6) / SUM($C$2:$C$6)Weighted Average in Excel 5
  2. This calculates the weighted average as 86.82
  3. Explanation: First, we used the SUMPRODUCT like we did in the previous example, but since the weights are not adding up to 1, we divide the result by the sum of all weights

This completes the different ways of calculating the weight average in Excel. To access the solution, please download the following file.

Weighted average Solution

Please share

Did you find this useful?  Why not share the love and tell your friends on the different social media platforms.

Join the newsletter & Get your CHEAT SHEET!

Enter your name and email to get our Excel Shortcuts cheat sheet and Join the Newsletter!

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to ConvertKit ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Abdelrahman Abdou
Latest posts by Abdelrahman Abdou (see all)
It's only fair to share...Share on Facebook
Facebook
0Pin on Pinterest
Pinterest
0Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin