Weighted Average in Excel

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.

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.

Weighted Average Explanation Eg

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

Formula of Weighted Average

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.read more 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:

  1. SUMPRODUCT() function &
  2. 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.

You can download this Weighted Average Excel Template here – Weighted Average Excel Template

  1. Draw a table with two columns with one column contains values & the other contains their weights.

    Weighted Average step 1

  2. 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.read more, i.e., multiply each value by its weight and then calculate their sum in order to get the sum-product.

    Weighted Average step 2

  3. Calculate the sum of the weights in another cell.

    Weighted Average step 3

  4. As the formula for calculating the weighted average suggests, now Divide the sum-product with the sum of weights.

    Weighted Average step 4

  5. And the resulting value will be the weighted average.

    Weighted Average step 5

Weighted Average Excel Examples

Example #1

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.

Weighted Average Example 1

#1 – In order to calculate the weighted average in excel, In any cell, write the formula,

Weighted Average Example 1-1

#2 – Press Enter, and we have our weighted average.

Weighted Average Example 1-2

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.

Weighted Average Example 2

#1 – In Excel to calculate the weighted average in excel, first, we need to calculate a SUMPRODUCT, write down the following formula,

Weighted Average Example 2-1

#2 – Now calculate the total number of values.

Weighted Average Example 2-2

#3 – Now, we can calculate the weighted average by dividing the SSUMPRODUCT with the SUM of the weightage assigned.

Weighted Average Example 2-3

#4 – Press Enter, and it returns the weighted average.

Weighted Average Example 2-4

Example #3

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.

Weighted Average Example 3

#1 – As discussed, first calculate SUMPRODUCT, write down the formula,

Weighted Average Example 3-1

#2 – Now we calculate the sum of the weightage,

Weighted Average Example 3-2

#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.read more of the weightage,

Weighted Average Example 3-3

#4 – Press Enter, and we have a weighted average for the employee.

Weighted Average Example 3-4

Things to Remember about Weighted Average formula in Excel

  1. SUMPRODUCT should be the sum of the products of the respective values and weights.
  2. We need to calculate the sum of the weights.
  3. The higher the weight, the more the effect will be on the value of the 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

Reader Interactions

Leave a Reply

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