How to Use AVERAGE.WEIGHTED Function in Google Sheets [2021]

The AVERAGE.WEIGHTED function in Google Sheets is useful when trying to find the average of a data set whose values have varying degrees of importance or frequency.

This function multiplies each number in the data set to their corresponding weight before taking their sum. The average value is taken as this sum divided by the total of the corresponding weights.

The Anatomy of the AVERAGE.WEIGHTED Function
A Real Example of using the AVERAGE.WEIGHTED Function
How to Use AVERAGE.WEIGHTED Function in Google Sheets

The AVERAGE.WEIGHTED function is a variation of the AVERAGE function in which all numbers in the data set are given equal weight. It is a less common but similar tool to the SUMPRODUCT function which multiplies a set of values to their corresponding pair in another range, then takes its sum.

Consider this example.

Say you are a high school teacher who just finished checking the final examination papers of your students. You then record their scores in a spreadsheet you made in Google Sheets. A snippet of the spreadsheet is shown below:

You need to calculate their final grade in your subject and submit it to the administrative office at the end of the day so the students’ grades may be printed on their report card. Simply taking the average of the scores for each requirement won’t reflect the student’s true class standing as each course requirement has different weights to their final grade: homework scores account for 15%, class participation accounts for 10%, and so on.

Manually multiplying each requirement score to their weights then taking their sum is both time-consuming and prone to errors. You have a total of 4 classes, with over 30 students for each. Considering these circumstances, the manual approach is not the most efficient way to tackle this problem. The AVERAGE.WEIGHTED function provides a fast and easy way to solve this problem! But how do you use the AVERAGE.WEIGHTED function? Let me tell you all that you need to know in using this function!

The Anatomy of the AVERAGE.WEIGHTED Function

So the syntax (the way we write) the AVERAGE.WEIGHTED function is as follows:

=AVERAGE.WEIGHTED(values, weights, [additional_values], [additional_weights])

Let’s dissect this thing and understand what each of these terms means:

= the equal sign is just how we start any function in Google Sheets.
AVERAGE.WEIGHTED() this is our AVERAGE.WEIGHTED function. It multiplies the values with the corresponding list of weights, takes their sum, and then divides this total with the sum of weights.
values is the list of values to be averaged. A cell reference, range of cells, or the values themselves may be used.
weights is the corresponding list of weights to be applied. A cell reference, range of cells, or the weights themselves may be used. Negative values are not allowed, but zero is permitted. If a range is used, it must be of the same size (same number of rows and columns) as the range of values.
additional_values provides another set of values to be averaged. The square brackets ‘[]’ indicate that this is an optional parameter. The same rule of having a cell reference, range of cells, or the values themselves in this argument is applied.
additional_weights provides the corresponding list of weights to be applied for additional_values. The square brackets ‘[]’ indicate that this is an optional parameter. The same rules for weights are applied. If a range is used, it must be of the same size as the range of additional_weights.

A Real Example of using the AVERAGE.WEIGHTED Function

Take a look at the example below to see how AVERAGE.WEIGHTED functions are used in Google Sheets.

For the given example, the average price of all the items is calculated. For each item, its corresponding price is multiplied by the quantity; the product is then listed in column D. The sum of the quantities and the products are then taken in cells C7 and D7, respectively. One can see that by using the SUMPRODUCT function, the equivalent value for D7 may be taken, just with fewer steps.

The sum of the products is then divided by the total quantity, as shown in cell D10. This value can be obtained without going through all the previous steps simply by using the AVERAGE.WEIGHTED function, as shown in cells D11 to D13.

The additional_values and additional_weights parameters of the function may be utilized when these values are located separately. As shown in cells D12 and D13, the same value is obtained as long as the additional_values and additional_weights parameters are paired correctly.

You may make a copy of the spreadsheet using the link I have attached below:

Make a copy of example spreadsheet

How to Use AVERAGE.WEIGHTED Function in Google Sheets

Going back to the scenario earlier, let’s try to calculate for the final grades of the students using the AVERAGE.WEIGHTED function.

Simply click on any cell to make it the active cell. For this guide, I will be selecting H3 where I want to show my result.

Next, simply type the equal sign ‘=’ to begin the function and then followed by the name of the function which is our ‘average.weighted’ (or ’AVERAGE.WEIGHTED’, whichever works). You should find that as you are typing, an auto-suggest box appears with the names of the functions that contain the text that you have typed.

The one we want is the AVERAGE.WEIGHTED function so make sure to click on the AVERAGE.WEIGHTED function. Alternatively, you may select the function by pressing the arrow down keys then pressing Enter or Tab to use the function (the currently selected function will be highlighted in grey and have a brief description below).

Upon selecting a function, a large text box appears that gives details about the function, and how to use the function. In some instances, a blue question mark will appear on the left side of the cell. If you want this text box to appear, simply click this question mark to show the large text box. Clicking on the arrow at the top right-hand corner will minimize the box while clicking on the x mark closes the text box and the blue question mark will appear.

Now, select the values that need to be averaged. In this case, we shall select the scores of the student in row 3. The quickest way to do so is by selecting a range: simply click on the first value (C3), press and hold the Shift key, then select the last value in the range (G3). You will notice that all the cells from C3 to G3 are selected and the text in the formula shows C3:G3. This indicates that you want the values of the range from C3 to G3.

Next, we have to select the corresponding weights for the values that were selected. First, type a comma ‘,’ to indicate that we are finished giving the values argument, and would like to input the weights argument. Select the range from C2 to G2 using the process explained in the previous step. Since the grades of the students are based on the same criteria, let’s convert this cell reference to an absolute cell reference. (An absolute cell reference allows the same cell/s to be referenced when the formula is copied) Simply click on the F4 key. You should notice that from the C2:G2 text in the formula, it now turned to &C&2:&G&2.

Finally, just hit the Enter key. A text box may pop up indicating an auto-fill suggestion by Google Sheets. If you would like to apply the auto-fill suggestion, press the Ctrl+Enter keys or click on the check button.

To complete the table, simply copy the formula to the other rows.

That’s pretty much it. You can now use AVERAGE.WEIGHTED functions in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.