A detailed tutorial on how to calculate the weighted averages in excel with an example.
This tutorial helps you on how to calculate weighted average in Excel or any other office tool such as LibreOffice Calc for a given set of values.
Weighted average is different from the ordinary (un-weighted) average or mean. In an un-weighted average each data item is given the same priority/weight/importance, and you just simply sum up all the data items and divide by the total number of data items.
In a weighted average, each data item is assigned with a priority/importance/weight and depending upon its weight, each data item becomes as big as its weight. That is each data item is multiplied by its weight, so that the data item with more weight becomes higher than its original value and then summed up. Then this sum of all data items is divided by the total weights of all data items.
Given below is the explanation of Un-weighted average and Weighted Average with an example:
Consider Marks of a student in her 12th Board Exams: Maths (89), Physics (92), Chemistry (83), Biology (94), Tamil (95), English (87).
Un-weighted Average = (89+92+83+94+95+87)/6 = 540/6 = 90
When the student wishes to apply for any Maths degree, the Maths mark is given priority when compared to other subjects. Here the Maths marks is given the weighttage of 3.
Weighted Average for Maths Major = (89*3)+(92*1)+(83*1)+(94*1)+(95*1)+(87*1) / 3+1+1+1+1+1
When the student wishes to apply for any Biology related degree, the Biology mark is given priority when compared to other subjects. Here the Biology mark is given the weighttage of 3.
Weighted Average for Biology Major = (89*1)+(92*1)+(83*1)+(94*3)+(95*1)+(87*1) / 3+1+1+1+1+1
Calculating Weighted Average in Excel:
As you have understood the calculation of weighted average manually, lets now move to understanding the calculation of weighted average in excel, using formula. In excel, weighted average is calculated using the SUMPRODUCT() and SUM() functions.
Formula for calculating weighted average in excel is given below:
= SUMPRODUCT(<Range of all Elements>, <Range of weights of all Elements>) / SUM(<Range of weights of all Elements>)
which is also written as,
= SUM((1st Element * its weight), (2nd Element * its weight),..,(nth Element * its weight)) / SUM(Weight of 1st Element, Weight of 2nd Element, ...,Weight of nth Element)
Follow the below steps to calculate weighted average in excel.
Step 1: Enter the data in a table as shown in the above images.
Step 2: Make sure you enter the weightage column as shown. In this example, we have given the weightage of 3 to the subject of Biology.
Step 3: In the cell D9, type the formula as "= SUMPRODUCT(C3:C8,D3:D8)/SUM(D3:D8)".
Step 4: Using the above formula gives the result 91 in D9 cell.
NOTE: This formula in Excel applies to Microsoft Office Excel 2003, Microsoft Office Excel 2007, Microsoft Excel 2010, Excel 2016, Microsoft Excel 2013 and LibreOffice Calc in Linux.