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:

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

- Click the last column named
**Add Column**and rename it to**Weighted Score (or Value)**. - Select the first cell in the newly created column and click the formula bar.
- 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:

- Select a cell just below the
**Values Area Divider**on the**Weights**column. - 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:

- Go to the source data worksheet.
- Click the
**Insert**tab and select the**PivotTable**drop-down menu. - There, choose
**From Data Model**. - 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:

- Press
`Alt`+`F11`to launch the**Excel VBA Editor**. - Click the
**Insert**button on the menu bar. - Choose the
**Module**option. - 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
```

- Hit the
**Save**button. **Close**the Excel VBA Editor.

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

- Press
`Alt`+`F8`to launch the**Macro**dialog box. - Select the
**CalculateWeightedAverage**macro. - Click
**Run**to execute the code. - 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:

- Click the
**Automate**tab on the**Excel ribbon**menu. - Hit the
**New Script**button inside the**Scripting Tools**block. - A
**Code Editor**will open on the right side. - 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");
}
```

- Click the
**Save script**button to save the code. - Hit
**Run**to execute the script. - 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.

## 0 Comments