By following this article, you will learn to calculate weighted average in Excel. Weâ€™ll use a simple formula, **SUM** function, and the combination of **SUMPRODUCT** and **SUM** functions whether or not the weights add up to 100.

Weighted averages are useful in various fields like finance, grade calculation, inventory valuation, quality control, project management, and market research.

Whether determining the cost of capital, calculating grades, valuing inventory, or analyzing customer satisfaction, weighted averages provide a versatile tool to reflect the relative importance of data points in diverse applications.

**Download Practice Book**

You can download the free Excel workbook here and practice on your own.

**Table of Contents**Expand

## What Is Weighted Average?

Weighted Average is a statistical measure that takes into account the importance or weight assigned to each data point when calculating the average. It gives more significance to certain data points based on their assigned weights.

This results in a more accurate representation of the overall average. The weight reflects each data pointâ€™s relative importance or relevance in the calculation.

The key distinction between Average and Weighted Average is that the former treats all data points equally, while the latter incorporates data point weights to give more significance to certain values.

## How to Calculate Weighted Average in Excel (When Weights Add Upto 100%)

### Method 1: Using Simple Formula

First, inside cell **B12**, type the following formula:

`=C5*D5+C6*D6+C7*D7+C8*D8+C9*D9`

Press **Enter**. The weighted average is calculated.

**Note:**Keep the format of the weight column in percentage.

### Method 2: Using SUMPRODUCT Function

Inside cell **B12**, write the following formula:

`=SUMPRODUCT(C5:C9,D5:D9)`

Press **Enter**. This method is more convenient than the previous one.

**Note:**Keep the format of the weight column in percentage.

## How to Calculate Weighted Average in Excel (When Weights Do Not Add upto 100%)

### Method 1: Using SUM Function

In cell **E5** you can see that the weights add up to 120%. So, in this case, you need to divide the result obtained using the simple formula by summation of the weights which can be calculated using **SUM** function.

In cell **B12**, put the following formula:

`=(C5*D5+C6*D6+C7*D7+C8*D8+C9*D9)/SUM(D5:D9)`

Press **Enter**.

### Method 2: Combining SUMPRODUCT and SUM Functions

Again, in this case you need to divide the result obtained using **SUMPRODUCT** function by summation of the weights which can be calculated using **SUM** function.

In cell **B12**, write the following formula:

`=SUMPRODUCT(C5:C9,D5:D9)/SUM(D5:D9)`

Press **Enter**. The average is calculated in accordance with their assigned weights.

## Solution to Data and Weights Having Different Array Types

The **SUMPRODUCT** function in Excel can take multiple arrays as input. But all the arrays need to be in the same dimension. Otherwise, the formula will result in an **#Value!** Error.

We can use the **TRANSPOSE** function to change the array type. Inside cell **D5** put the following formula:

`=TRANSPOSE(C13:G13)`

Press **Enter**.

The Weight Percentage array in now turned into a vertical array from a horizontal one.

Now, inside cell **F5**, type the following formula:

`=SUMPRODUCT(C5:C9,D5:D9)/SUM(D5:D9)`

Press **Enter**.

## Things to Remember

- Before starting any calculation, check if the assigned weight percentages add upto 100 or not.
- Ensure that your data is properly organized with two columns: one for the values and another for the corresponding weights
- Make sure there are no missing or erroneous weights that could affect the accuracy of the weighted average.
- If you have zero or blank values in your data set, consider excluding them from the calculation of the weighted average. Including them might distort the result and produce inaccurate averages.

## Frequently Asked Questions

**Is SUMPRODUCT a weighted average?**

Not quite. We use the **SUMPRODUCT** and **SUM** functions together to determine the Weighted Average.

**What is weighted vs regular average?**

Weighted averages incorporate weights to give more significance to specific values, while regular averages treat all values equally in the calculation.

## Conclusion

By going through this article, I hope you can now effectively work with weighted average in Excel and obtain accurate results based on the assigned weights.

To further expand your knowledge and expertise in Excel, I encourage you to visit Exceldemy, a valuable resource for in-depth information on various uses of Microsoft Excel. There, you can explore a wide range of tutorials, tips, and tricks that will empower you to leverage the full potential of this powerful spreadsheet software.

## Weighted Average Excel: Knowledge Hub

**<<Go Back toÂ How to Calculate Average in Excel | How to Calculate in Excel | Learn Excel**