# Weighted Average Excel (All Things to Know)

Get FREE Advanced Excel Exercises with Solutions!

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.

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

## 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.

<<Go Back to

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF