You must be precise if you’re analyzing data for actionable insights. Enter the realm of calculating weighted averages in Excel. A skill that amplifies the accuracy of your insights.

Beyond simple averages, the weighted average in Excel factors in the weightage of different data points, offering a granular perspective. Whether you’re deciphering market trends, student performance, or project evaluations, mastering this Excel function is a game-changer.

This article on weighted averages helps you with the mechanics of weighted averages, providing clarity on concepts and practical implementation.

Elevate your analytical prowess and make your numbers tell a more meaningful story with this comprehensive Excel tutorial.

What Is the Weighted Average?

A weighted average is a statistical method that assigns different levels of importance, or “weights,” to various data points based on their significance or relevance.

This approach accounts for the varying impact each data point has on the final average. Heavier weights amplify the influence of certain values, while lighter weights reduce their impact.

This technique is commonly used in diverse fields like finance, grading systems, and data analysis. By incorporating weights, a weighted average provides a more accurate representation of the overall data set, offering insights that align with real-world scenarios and priorities.

Find below where you can use weighted averages:

• This statistical function offers more accurate insights into data trends than arithmetic averages.
• Weighted average calculation aids in financial analysis by considering different capital contributions.
• In a partnership business, you can fairly distribute profits between all the partners considering their individual contributions to the business.
• In grading systems, it ensures fairness by factoring in assignment and test weights.
• In market research, it reflects differing impacts of various market segments.
• It’s easy and efficient to allocate resources for a project based on the weighted average of the tasks being performed rather than following the arithmetic average.

Reasons to Calculate Weighted Averages in Excel

• You can get the weighted average value of a table of large datasets in Excel in less than a second if you use the right functions or data analysis tools.
• Also, you can automate the calculation process using advanced Excel tools like Excel VBA, Power Query, Office Scripts, etc.
• Excel helps you to mitigate the human error that could arise from the manual calculation of the weighted average of a dataset in pen and paper.
• You can easily present your findings in front of an audience or share the results via link sharing from Microsoft OneDrive, SharePoint, etc.
• Moreover, you can visualize your findings using charts, graphs, or conditional formatting in Excel.

Now that you’re all set to calculate weighted average in Excel, follow along with the easy and quick methods mentioned below:

Use the Weighted Average Formula in Excel

There aren’t any dedicated weighted average formulas in Excel like the arithmetic average formula: AVERAGE. However, you can use the formula SUMPRODUCT to get the weighted average in Excel.

At times, you’ll also need to use the SUM formula with SUMPRODUCT to get weighted average values.

A Case When Weights Add Up to 100

Suppose, your dataset contains values and respective weights for those values. The sum of weights is 100. Then, you only need to use the SUMPRODUCT formula. Find below the scenario and steps to do this:

Suppose your business procured capital from five different sources at different interest rates. The amount of capital used from these sources comes with percentages of weights. See the example above.

To calculate the weighted average here, enter the following formula in the cell where you want this value:

=SUMPRODUCT(B2:B6,D2:D6)

Now, hit Enter to get the weighted average for the above dataset in a decimal number.

To convert the decimal value to a percentage, use this formula:

=B8*100

In the above, you’re multiplying the decimal value with 100 to get a percentage-weighted average value.

Here’s how you need to modify the above formula to make it work in your worksheet:

• The cell range B2:B6 represents the cost or interests of the capital used. Change this cell range according to your own worksheet.
• Similarly, D2:D6 represents the respective weightage for each capital source. You also need to change this range.

Just keep in mind that you are entering at least two arrays. The first array should be a range of values from which you want to calculate the weighted average. The second array should be a weight or percentage summing up to 100.

A Case When Weights Don’t Add Up to 100

If you’re using a weight scale like 1 to 10 or 1 to 5 to indicate the priority of the linked value, then you need to divide the SUMPRODUCT value with SUM or the weights. Then, you get the accurate weighted average.

The above dataset shows a high school student securing scores in different courses. However, when the institute is going to assign a grade, they give weightage to different courses according to their importance, complexity, etc.

Then a weighted average is calculated to assign the final grade.

In a cell of your choice, enter the following formula and hit Enter to find the weighted average secured by the student:

=(SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6))

Since the weights allocated to the courses don’t sum up to 100, you’ll need to divide SUMPRODUCT by SUM of weights.

Use Power Pivot to Calculate Weighted Average in Excel

If you want to visualize the weighted average through a PivotTable, you can calculate it in Power Pivot and then import it into a PivotTable. Here’s how you can do so:

1. Select the dataset that contains the input values and click the Power Pivot tab.
2. Click the Add to Model button inside the Tables block to include the dataset in the Power Pivot tool.
3. You should now see your dataset inside Power Pivot as various columns.

4. Click the last column named Add Column and rename it to Weighted Score (or Value).
5. Select the first cell in the newly created column and click the formula bar.
6. Enter the following formula into it

=Table1[Test Score]*Table1[Weights (1 to 10)]

So far, you’ve multiplied the given values with their respective weight values.

Now, you also need to sum up the Weights and Weighted Score (Value) column as outlined below:

1. Select a cell just below the Values Area Divider on the Weights column.
2. Enter the following formula into it:

Sum of Weights (1 to 10):=SUM([Weights (1 to 10)])

Similarly, select a cell below the Power Pivot Divider on the Weighted Score (Value) column and enter this formula:

Sum of Weighted Score:=SUM([Weighted Score])

Upon pressing Enter in both of the above cases, Power Pivot calculates the sum for the selected columns.

Now, simply select any cell below the Values Area Divider and enter this formula to get the weighted average in Excel:

Measure 1:=[Sum of Weighted Score]/[Sum of Weights (1 to 10)]

Click the File menu on the Power Pivot tool and hit Save and close the tool.

So far, on Power Pivot, you multiplied the given values with the relevant weights to get a weighted value. Then, you created individual summation values of the weights and weighted values column.

By dividing the sum of weighted values by the sum of weights, you get the weighted average.

All the above formulas simply use the column headers of the dataset. So, modify the above formulas with the matching columns in your worksheet to get these formulas to work for you.

To visualize the weighted average in a PivotTable, follow these steps:

1. Go to the source data worksheet.
2. Click the Insert tab and select the PivotTable drop-down menu.
3. There, choose From Data Model.
4. On the PivotTable from Data Model dialog box, click Existing Worksheet, and select OK.

By now, you should see the PivotTable box on the worksheet. On the right side of the box, you should also see the PivotTable Fields panel.

There, click Table1 to expand its contents and checkmark the checkbox for Measure 1 to visualize the weighted average value on your PivotTable.

Calculate Weighted Average in Excel Using Excel VBA

If you know a little bit about Excel VBA coding, you should use the following VBA script to automate the whole process of calculating a weighted average. Find below the steps to use the code as well:

1. Press Alt + F11 to launch the Excel VBA Editor.
2. Click the Insert button on the menu bar.
3. Choose the Module option.
4. In the blank Module, copy and paste this script:

Sub CalculateWeightedAverage()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim totalWeightedScore As Double
    Dim totalWeight As Double
    Dim weightedAverage As Double
    
    Set ws = ThisWorkbook.Sheets("Sheet3")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    totalWeightedScore = 0
    totalWeight = 0
    
    For i = 2 To lastRow
        totalWeightedScore = totalWeightedScore + ws.Cells(i, "B").Value * ws.Cells(i, "C").Value
        totalWeight = totalWeight + ws.Cells(i, "C").Value
    Next i
    
    If totalWeight <> 0 Then
        weightedAverage = totalWeightedScore / totalWeight
    Else
        weightedAverage = 0
    End If
    
    ws.Cells(8, "B").Value = weightedAverage
End Sub

5. Hit the Save button.
6. Close the Excel VBA Editor.

Now, it’s time to run the script to automatically calculate the required value. Here’s how:

1. Press Alt + F8 to launch the Macro dialog box.
2. Select the CalculateWeightedAverage macro.
3. Click Run to execute the code.
4. You should see the value in the designated cell on the worksheet.

To modify the code to make it work on your worksheet, follow these instructions:

• Rename Sheet3 to the actual worksheet name.
• Put all the values in column B and respective weights in column C.
• If you want to use your own columns, then change the occurrences of column B to the column header of the values column. Do the same for column C, which should be the weights of the given values.
• Modify the ws.Cells(8, "B").Value code element to the actual cell address where you want the weighted average, like ws.Cells(10, "F").Value.

Calculate Weighted Average in Excel Using Office Scripts

If you got Microsoft 365 Business Standard or a better subscription, you can use Office Scripts to automate the task on Excel on the Web and Excel for Microsoft 365 desktop app. Find here the scripts and steps you need to follow:

1. Click the Automate tab on the Excel ribbon menu.
2. Hit the New Script button inside the Scripting Tools block.
3. A Code Editor will open on the right side.
4. There, copy and paste the following Office Scripts code by replacing the existing code blocks:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range B8 on selectedSheet
	selectedSheet.getRange("B8").setFormulaLocal("=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)");
	// Set number format for range B8 on selectedSheet
	selectedSheet.getRange("B8").setNumberFormatLocal("0.00");
}

5. Click the Save script button to save the code.
6. Hit Run to execute the script.
7. Excel will calculate the weighted average and put it in the cell B8.

Here’s how you can modify the above Office Scripts code:

• B8 is the destination for the weighted average output, so change it to another cell address if you need to.
• B2:B6 cell reference represents the weights values of the given scores or other kinds of values. So, you also need to change it referencing your worksheet data.
• C2:C6 represents the scores or values given for weighted average calculation. So, change this cell reference that matches your dataset.

Conclusion

So, now you know all the best and easiest ways to calculate weighted average in Excel. If your source dataset is small and manageable, you can go for the SUMPRODUCT formula to calculate this value.

If your dataset is huge, you must choose Excel VBA or Office Scripts.

Finally, if you’re working on a PivotTable and need to calculate the weighted average easily, use the Power Pivot-based method.