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.

Are you still looking for help with the Average function? View our comprehensive round-up of Average function tutorials here.