In this article:

How to Use AVERAGE.WEIGHTED Function in Google Sheets

We need to process data such that they will yield a score that is the sum of weighted averages. One familiar example are scores and grades at school:

Original data

A useful function for this purpose is the AVERAGE.WEIGHTED function. Read this article to learn more about how to use this function and how it compares with SUMPRODUCT, a related but different function. Are you ready?

How to use AVERAGE.WEIGHTED function

Relative Weights

When we want to calculate the weighted averages, we need to set a column or a row to hold the relative weights of each data. For our example, we will store the relative weights as follows in Row 1:

Portion of original data with corresponding weights

As you may point out, we normally store weights as either decimals or percentages rather than whole numbers as in this example. We will point out why we use this format later on.

AVERAGE.WEIGHTED Syntax

The AVERAGE.WEIGHTED function has the following syntax:

AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights])

Where values refer to the actual values or scores, while weights refer to the corresponding weights of the values. You can include the actual values and weights in the function or point it to a cell or array. We will point the function to the data as follows:

Various valid syntaxes of AVERAGE.WEIGHTED function

Here are the examples of AVERAGE.WEIGHTED syntaxes, explained:

  • average.weighted(B3,B1,C3,C1,D3,D1,E3,E1,F3,F1) - the value and their weights are individually listed in pairs
  • average.weighted(B3:F3,B1:F1) - the range of values are specified first and then the range of their corresponding weights. This works if there is a clear correspondence, such as when the values are stored in a single row and the weights are on another row and the pairs are in the same column, just like in the example.
  • average.weighted(B3:F3,$B$1:$F$1) - same as the second example, but the range of weights are fixed. This is useful when you want to simply drag down the equation to be copied to other rows, for example. 

The third example is especially useful if you have either rows or columns of entries where the same AVERAGE.WEIGHTED function needs to be applied, as shown below, because the weights will stay the same in each calculation and only the values will change.

Scores with weighted averages calculated via AVERAGE.WEIGHTED function

AVERAGE.WEIGHTED vs SUMPRODUCT function

As you might have noticed, another similar function is the SUMPRODUCT function. There is an important difference between the two, however. It may not be apparent if you store the relative weights of values in the form of decimals or percentages, as show below:

AVERAGE.WEIGHTED vs SUMPRODUCT functions for weights expressed in percentage

If you store them in whole numbers, however, the difference emerges:

AVERAGE.WEIGHTED vs SUMPRODUCT functions for weights expressed in whole numbers

The AVERAGE.WEIGHTED function is superior in this regard because it automatically adjusts the relative weights in whole numbers into its decimals or percentages, and then proceeds to calculate the weighted average of the values.

Sometimes you need to adjust the weights over time, and it can take some time to calculate their decimal or percentage equivalents. AVERAGE.WEIGHTED automatically handles it so you can go on with your analysis of the results.

Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!