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 portfoliosInvestment PortfoliosPortfolio investments are investments made in a group of assets (equity, debt, mutual funds, derivatives or even bitcoins) instead of a single asset with the objective of earning returns that are proportional to the investor's risk profile., 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 purposes, let us alter the values, in example 3, and see how the weighted average affects. 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.
The formula for Weighted Average in Excel
Where ‘w’ stands for ‘Relative weight’ in % &,
‘x’ stands for ‘value.’
The above-depicted weighted average formulaWeighted Average FormulaThe weighted average formula is simply summing up the products of each value with its respective weightage. Here, more significance is given to the weightage of the values rather than the variables themselves. 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.
- Draw a table with two columns with one column contains values & the other contains their weights.
- Calculate the SUMPRODUCTCalculate The SUMPRODUCTThe SUMPRODUCT function in excel is used to multiply the array or range and provide the sum of a product. It is to calculate a weighted average. SUMPRODUCT formula is used to calculate the sum of corresponding numbers product in one or more ranges/arrays., i.e., multiply each value by its weight and then calculate their sum in order to get the sum-product.
- Calculate the sum of the weights in another cell.
- As the formula for calculating the weighted average suggests, now Divide the sum-product with the sum of weights.
- And the resulting value will be the weighted average.
Weighted Average Excel Examples
First, let us start with the basic example; we have values in the A column and weights in the 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.
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.
Suppose for an employee. The performance is defined by few weightage as 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 excelSum In ExcelThe SUM function in excel adds the numerical values in a range of cells. It is categorized under the math and trigonometry function entered by typing “=SUM” followed by the values to be summed. The values can be numbers, cell references or ranges. of the weightage,
#4 – Press Enter, and we have a 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 the weighted average.
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 –