Calculating Weighted Average in Excel

The concept of 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 same weight or priority to each value, this is called 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:


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 same significance to each number.

So, what is the Weighted Average then?

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:


Now, let’s consider the student want an 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.


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 the Economics marks have contributed more to the final result than any other element.

How to Calculate Weighted average in Excel?

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

So, for this you have two methods:

Method 1: Calculating Weighted Average by using Sum Function:

This is an easy method and it requires you to have knowledge of 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 but it is not a feasible option if you have large number of elements.

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

This method requires you to have 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 on Calculating Weighted Average in Excel:

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


As you can see that, 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 into 61.23

Alternatively, 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]


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

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

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.

Privacy Policy | Disclaimer | About