How To Calculate Weighted Average In Google Sheets (Weighted Average Formula)

By

You can calculate weighted averages in Google Sheets by using a built-in formula: AVERAGE.WEIGHTED.

Weighted averages are useful for calculating average scores when some factors are more important than others.

What is weighted average? A weighted average is similar to an average of numbers, but each number makes up a different share of the average.

For example, you may have a class where there are two tests worth a different portion of your grade: the midterm is 40 percent while the final is 60 percent.

The AVERAGE.WEIGHTED function expands on the AVERAGE function.

Additionally, Google Sheets and Microsoft Excel both support a similar function called SUMPRODUCT, but the AVERAGE.WEIGHTED function can return the results you’re looking for with less effort in most cases.

The following processes will teach you how to calculate weighted average using the AVERAGE.WEIGHTED function in Google Sheets.

Calculate Weighted Average In Google Sheets using Percentage Weights

The weighted average formula in Google Sheets uses two data set inputs:

  • the values and
  • the weights

Below is the syntax of the Average.Weighted function:

=AVERAGE.WEIGHTED(values, weights)

When you’ve added values, it will look something like:

=AVERAGE.WEIGHTED(A1:A5,B1:B5)

We’ll look at how this function works with the following class grades dataset.

Dataset with test scores and weights

The class featured two tests making up 75 and 25 percent of the final grade respectively. The example uses percent values to establish the weighting.

The following steps explain how to calculate the weighted average in Google Sheets:

  1. Determine the “values” criteria for the formula. In this case, we’ll be combining the values in columns B and C for the students, so our value will be “B2:C2” for the formula in cell D2. It will grab from the same row B and C cells when expanded to the rest of column D. Range to use to get the weighted average
  2. Determine the “weighted” criteria for the formula. In our example, the weight values are stored in cells B7 and C7. Since we don’t want these values to change, we need to set them to static values, so our weighted criteria will be “$B$7:$C$7.” In the example, we are using “%” scores to assign weights. Weights to be used in percentages
  3. Add the criteria to the formula =AVERAGE.WEIGHTED(values, weights). The example formula is
    =AVERAGE.WEIGHTED(B2:C2, $B$7:$C$7)
  4. Enter the formula in the topmost cell for the return values column. The example shows adding the formula to cell D2. AVERAGE formula to calculate the weighted average
  5. This will return the results for cell D2; drag the bottom right icon in the cell for the rest of the rows to apply the formula across the spreadsheet. Copy and paste the formula for the entire column

The formula will return the weighted averages. With James, the formula is calculating =SUM((B2*0.75)+(C2*0.25)), but uses a syntax that will update for all cells if you change the weighted value.

Additionally, manually building weighted calculations can get very cumbersome if you have more than a few values to average.

Calculate Weighted Average in Google Sheets with Quantity Weights

The convenience of knowing how much each value in the average will be weighted by a percentage is a luxury you don’t get in a lot of cases.

In this example, we’ll determine the average selling price of a product at a hypothetical store. Column A has the product names, Column B has the selling price, and Column C contains the quantity sold.

Price and Quantity data

Unlike the previous example, this one doesn’t use a percentage value to determine weight. Instead, the formula will calculate the weight percentages automatically based on the sum of the values.

In this version, the price is the value, but the quantity sold values are the weights.

Follow these steps to use the WEIGHTED.AVERAGE formula to calculate the average selling price

  • Determine the “values” criteria for the formula. In this case, we’ll be combining the values in column B. We don’t want these values to change, so the example value criteria are: “$B$2:$B$5” Price of the gadgets
  • Determine the “weighted” criteria for the formula. In our example, the weight values are stored in column C. We don’t want these values to change, so the example weighted criteria are: “$C$2:$C$5” Quantity of the gadgets
  • Add the criteria to the formula =AVERAGE.WEIGHTED(values, weights). The example formula is
    =AVERAGE.WEIGHTED($B$2:$B$5,$C$2:$C$5)
  • Enter the formula in the return cell to run the function. In the example, the formula returns a rounded value of “94.4. Weighted average using the AVERAGE WEIGHTED formula

Calculate Weighted Average in Google Sheets with Additional Values

You can expand on how the =AVERAGE.WEIGHTED function works by adding additional values. This feature is useful if you need to add weighted values outside of the predetermined range.

For example, a teacher might throw some students a freebie with their grades by adding an additional weighted assignment with a perfect score to the grade calculation.

For this example, we’ll use the same grades from the first example, except we’ll change the weight values and add two “perfect score” bonus assignments for all students.

Data with bonus score and weight

The appended formula looks like this:

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

When filled out with one additional value it looks like this:

=AVERAGE.WEIGHTED(A1:A5, B1:B5, C5, D5)

When filled out with two additional values it looks like this:

=AVERAGE.WEIGHTED(A1:A5, B1:B5, C5, D5, C6, D6)

Notice that the formula uses the pattern “additional value 1, additional weight 1, additional value 2, additional weight 2.” It goes “value, weight” repeat as opposed to “all values, all weights.”

Follow these steps to calculate the weighted average with additional weights:

  • Determine the “values” criteria for the formula. In this case, we’ll be combining the values in columns B and C for the students, so our value will be “B2:C2” for the formula in cell D2. It will grab from the same row B and C cells when expanded to the rest of column D. Select the scores for the first student
  • Determine the “weighted” criteria for the formula. In our example, the weight values are stored in cells B7 and C7. Since we don’t want these values to change, we need to set them to static values, so our weighted criteria will be “$B$7:$C$7.” Select the weightage
  • Determine the “additional values” and “additional weights” criteria for the formula. In this example, we’re bringing in additional values from cells B9 and C9, with their corresponding weights in cells B10 and C10. This is expressed as “$B$9,$B$10,$C$9,$C$10.” Select the bonus score and weight
  • Add the criteria to the formula =AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights]). The example formula is
    =AVERAGE.WEIGHTED(B2:C2,$B$7:$C$7,$B$9,$B$10,$C$9,$C$10).
  • Enter the formula in the topmost cell for the return values column. The example shows adding the formula to cell D2. Average weighted formula with bonus score
  • This will return the results for cell D2; drag the bottom right icon in the cell for the rest of the rows to apply the formula across the spreadsheet. Copy to the entire column

The average weighted formula can substantially cut down on the amount of work you need to do in Google Sheets to calculate weighted averages.

I hope you found this tutorial useful!

Other Google Sheets tutorials you may like:

Popular Posts