In this article:

How to Use AVERAGE.WEIGHTED Function in Google Sheets

May 8, 2024

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.


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


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.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->