Go Back

How to Calculate the Weighted Average in Excel

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I'm trying to determine the weighted average
Solved by Z. C. in 20 mins
Need a formula for weighted average
Solved by T. S. in 25 mins
I am looking for a way to weight baseball statistics - ex, batting average weighted by times at bat, earned run average weighted by innings pitched.
Solved by F. J. in 23 mins

Leave a Comment

avatar