When we want to calculate the average of a set of values with different levels of relevance, we compute for the weighted average. Excel allows us to calculate the weighted average using the **SUMPRODUCT **and **SUM **functions.

*Figure 1. Final result: Calculate the weighted average*

Final formula: **=SUMPRODUCT(C3:C7,D3:D7)/SUM(D3:D7)**

**Syntax of the SUMPRODUCT Function**

SUMPRODUCT returns the sum of the products of two or more components in the given arrays.

`=`

**SUMPRODUCT**(**array1**, [**array2**], [**array3**], ...)

where

**array**– a range of values or cells whose values we want to multiply with other values and then add- All array arguments must be in the same size
- Non-numeric values in an array are treated as zeros

**Syntax of SUM Function**

SUM adds all given values

**=SUM(number1,[number2],...])**

**number1**– any number, array or cell reference whose values we want to add- Only number1 is required; succeeding numbers are optional

**Setting up the Data**

Our table consists of three columns: Measures (column B), Rating (column C) and Weight (column D). In cell F3, we will calculate the weighted average of the ratings.

* Figure 2. Sample data to calculate the weighted average*

**Calculate the weighted average of the ratings**

In order to calculate the weighted average of the ratings, we follow these steps:

**Step 1**. Select cell F3

**Step 2**. Enter the formula: `=`

**SUMPRODUCT**(C3:C7,D3:D7)/**SUM**(D3:D7)

**Step 3**. Press Enter

The SUMPRODUCT returns the sum of the products of the ratings and weights (rating x weight). Actual calculation of the SUMPRODUCT looks like this:

`=(C3xD3) + (C4xD4) + (C5xD5)`

The SUM function returns the total of the weights.

The SUMPRODUCT divided by the SUM function returns the value of the weighted average of the ratings. As a result, the value in cell F3 is 90.70%.

*Figure 3. Using SUMPRODUCT and SUM to calculate weighted average*

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment