Calculating Weighted Average in Excel

The concept of an average comes from mathematics, average can be defined as the result obtained by adding several quantities together and then dividing this total by the number of quantities.

Usually, when we calculate average, we put the same weight or priority to each value, this is called an un-weighted average.

For example, let’s say we want to calculate the average of Marks of a Student in five subjects:

The marks are as follows:

Marks-of-Sutudent-in-5-subjects

So, we will sum the numbers and divide the result by 5 : (55 + 65 + 75 + 85 + 95)/5 = 75

This is the un-weighted average because in this case, we have assigned the same significance to each number.

What is the Weighted Average?

Weighted Average is a type of average where item weight is also taken into consideration while finding the average. And because of this one element may contribute more heavily to the final result than another element.

Let’s understand this with the below example:

The marks of the student in 5 subjects are as follows:

Marks-of-Sutudent-in-5-subjects

Now, let’s consider the student who wants admission to an Economics college. So, obviously, the college will put more emphasis on the Economics marks, and hence they have come up with an idea of assigning weight to each subject as follows.

Marks-of-Sutudent-in-5-subjects-with-weights

This shows that the college gives 3 times more emphasis on the marks of Economics than another subject while finding the average.

So, now we find the weighted average of the marks according to the above table as:

((55 x 1)+(65 x 1)+(75 x 1)+(85 x 3)+(95 x 1)) / (1+1+1+3+1)

This comes out to be = 77.85, which is higher than the un-weighted average i.e. 75.

So, in this case, Economics marks have contributed more to the final result than any other element.

How to Calculate the Weighted average in Excel?

After understanding the concept of the Weighted average in Excel, you must be thinking about how to calculate it in Excel.

So, for this you have two methods:

Method 1: Calculating the Weighted Average by using Sum Function

This is an easy method and it requires you to have knowledge of the SUM function. The formula is as follows:

= SUM((1st Element * Weight of 1st Element), (2nd Element * Weight of 2nd Element), … , (nth Element * Weight of nth Element)) / SUM(Weight of 1st Element, Weight of 2nd Element, Weight of nth Element)

Although this method is easier to understand it is not a feasible option if you have a large number of elements.

Method 2: Calculating the Weighted Average by using SUMPRODUCT Function [Easy Way]

This method requires you to have an understanding of Excel SUMPRODUCT function. The formula is as follows:

= SUMPRODUCT(<Range containing all the Elements>, <Range containing weight of all the Elements >)/ SUM(<Range containing weight of all the Elements >)

An Example of Calculating Weighted Average in Excel

The below table shows the data from a “Project Task Completion Plan” table.

Project-task-Completion-Plan

As you can see, in the above table there are five tasks each one with its own “priority” and “completion percentage”.

So, in this case, “Priority” will act as the weight assigned to completion percentage.

Now, to find the weighted Average we can use a formula:

=SUMPRODUCT(E3:E7,D3:D7)/SUM(D3:D7) //Method 2

Which results in 61.23

Alternatively, the Weighted Average can also be calculated using the formula:

=SUM(E3*D3,E4*D4,E5*D5,E6*D6,E7*D7)/SUM(D3:D7) //Method 1

Project-task-Completion-Plan

Note: In the above image notice the difference between the result of the un-weighted average and weighted average.

So, that was all about finding a weighted average in Excel. You can get the spreadsheet containing the above-discussed examples from here [link] and do let us know in case you face any issues while using any of the methods described above.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.