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.

##### Table of Contents

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:

**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.

**other numerous Google Sheets formulas**to create even more powerful formulas that can make your life much easier.