Weighted Average in excel is calculated when we assign each data set with some kind of weights like weightage, in statistics or portfolios we use weighted average to calculate more robust and impact observations and calculations, the formula for weighted average is very simple which is = (w1x1+w2x2+….+wnxn)/(w1+w2+..wn) where w is the weight assigned to the x value and we use sumproduct function to calculate the weighted average.
Weighted Average in Excel Calculation
Weighted Average is an average in which each observation in the data set is assigned or multiplied by weight before summing to a single average value.
- In this procedure, each quantity to be averaged is assigned a weight that determines the relative importance of each quantity. Weightings are the equivalent of having that many like items with the same value involved in the average.
- It is a more accurate measurement of scores, grades or investments that are of relative importance to each other. This is often the case with investment portfolios, grade scoring, and other statistics.
Explanation Of Weighted Average in Excel
In excel weighted Average, every value is assigned a specific weight, for example in example 3 the employee’s performance is most affected by his/her productivity as it has the most weight rather than the attendance, which has the least weightage.
For knowledge purpose let us alter the values in example 3 and see how the weighted average effects. Exchange the values of productivity and attendance with each other to see how the weighted average changes.
The value of the weighted average changed from 81 to 86 because productivity has the most weight.
Similarly, Attendance has less weight so the effect on the average is not so much.
Formula for Weighted Average in Excel
Where ‘w’ stands for ‘Relative weight’ in % &,
‘x’ stands for ‘value’.
The above-depicted weighted average formula is a basic mathematical formula in which the excel weighted average is calculated.
In Excel, we use two functions to calculate the weighted average in excel. They are:
- SUMPRODUCT() function &
- SUM () functions.
Steps to Calculate Weighted Average in Excel
There are few steps to calculate the weighted average in excel. Please find below the steps to calculate the weighted average in excel.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Step 1) Draw a table with two columns with one column contains values & the other contains their weights.
Step 2) Calculate the SUMPRODUCT i.e. multiply each value by its weight and then calculate their sum in order to get the sum product.
Step 3) Calculate the sum of the weights in another cell.
Step 4) As the formula for calculating the weighted average suggests, now Divide the sum-product with the sum of weights.
Step 5) And the resulting value will be the weighted average.
Weighted Average Excel Examples
Example #1
First, let us start with the basic example; we have values in A column and weights in B column. And we need to calculate the weighted average in excel. Let us start mathematically first.
#1 – In order to calculate the weighted average in excel, In any cell write the formula,
#2 – Press Enter and we have our weighted average.
Example #2
In a class where a student has his marks for different subjects and he/she has different weightage for each subject. The teacher needs to calculate the weighted average in excel for each subject.
#1 – In Excel to calculate the weighted average in excel, first, we need to calculate a SUMPRODUCT, write down the following formula,
#2 – Now calculate the total number of values.
#3 – Now we can calculate the weighted average by dividing the SSUMPRODUCT with the SUM of the weightage assigned.
#4 – Press Enter and it returns the weighted average.
Example #3
Suppose for an employee the performance is defined by few weightage like Productivity has the most weightage than compared to accuracy and accuracy has more weightage compared to attendance and so on.
#1 – As discussed, first calculate SUMPRODUCT, write down the formula,
#2 – Now we calculate the sum of the weightage,
#3 – To calculate the weighted average, divide the SUMPRODUCT with the sum in excel of the weightage,
#4 – Press Enter and we have the weighted average for the employee.
Things to Remember about Weighted Average formula in Excel
- SUMPRODUCT should be the sum of the products of the respective values and weights.
- We need to calculate the sum of the weights.
- The higher the weight the more the effect will be on the value of weighted average.
Recommended Articles
This has been a guide to Weighted Average in Excel. Here we discuss how to calculate Weighted Average using Excel Formulas (SUM and SUMPRODUCT) along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
