Calculating Weighted Average in Microsoft Excel

In this article we will learn about how to calculate weighted average in Microsoft Excel.

The concept of average comes from mathematics. Average can be defined as the result that we obtain after adding several quantities and then dividing this total by the number of quantities.

The weighted mean is similar to the arithmetic mean where instead of each of the data points contributing equally to the final average, some data points contribute more than others.

If all the weights are equal, then the weighted mean remains same as the arithmetic mean.
Let us understand more with an example:

We want to calculate the average of Marks of a Student in four subjects. In column A, we have Subject & in column B we have Marks

img1

  • We will add the numbers & then divide the result by number of subjects
  • (75 + 90 + 65 + 85) / 4 = 78.5
  • This is the un-weighted average because in this scenario, we have assigned same priority to each number.

Weighted Average is a type of average where item of the weight is also taken into account while finding the average,because this one element may contribute heavily to the final result than another element.
Now let us understand with another example

In column A, we have Subject & in column B, we have Marks

img2

Let us assume that a student want to get admission to Math college because of interest in Mathematics.The College team has realized that a higher percentage of students want to get admission in their college because of good Faculties available & their excellent track record for the Math subject.So, obviously the college will put more emphasis on the Math marks and hence, they have come up with an idea of assigning weight to each subject as follows:

img3

  • This shows the college lays more emphasis on the marks of Math as compared to marks of other subjects
  • If we multiply Marks with Weight i.e. (Marks x Weight) we get:((75 x 1) + (90 x 2) + (65 x 1) + (85 x 1)) / (1 + 2 + 1 + 1)
  • This will come out to be 81

img4

  • Weighted Average (81) is higher than the Un-weighted Average (78.5)
  • So, in this case, the Math’s marks have contributed more to the final output than any other element.
  • With the above shown example, we have understood the difference between Weighted & Un-weighted Average.

Users are saying about us...

  1. Seems like this article is more about mathematics (the average is not 78.5, though) than Excel.
    I think, the beef here should be the SUMPRODUCT formula, i.e.:
    =SUMPRODUCT(B2:B5;C2:C5)/SUM(C2:C5)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube