In this tutorial, I will show you how you are getting weighted averages in Google Sheets.
A weighted average is a form of average that takes into account how much a particular value contributes to the whole.
It can be calculated in Google Sheets manually with the help of additional columns, through the use of the SUMPRODUCT & SUM formula, or with the use of the AVERAGE.WEIGHTED function.
How to use the AVERAGE.WEIGHTED function in Google Sheets
The Syntax for AVERAGE.WEIGHTED is AVERAGE.WEIGHTED(values, weights, [additional_values, …], [additional_weights, …])
Methods For Getting Weighted Averages in Google Sheets:
- Replace the “values” with the range of values, then “weights” with the range of weights, then enter formula into a cell:
=AVERAGE.WEIGHTED(values, weights) - Replace the “values” with the range of values, then “weights” with the range of weights, then enter formula into a cell:
=SUMPRODUCT(“values”,”weights”)/SUM(“weights”) - Replace the “weight” with an individual weight, then ”weight_range” with the range of the weights, in absolute reference form ($A$1:$A$10), enter into a cell in a new column, then copy to the rest of the rows that correspond with your “weight_range”:
=weight/SUM(weight_range)
Next, multiply the values, with the resulting weight in their corresponding rows,
then add all the resulting values
Weighted Averages in Google Sheets Video Tutorial
The AVERAGE.WEIGHTED function in Google Sheets explained:
The Syntax for AVERAGE.WEIGHTED is AVERAGE.WEIGHTED(values, weights, [additional_values, …], [additional_weights, …])
values: raw values assigned to a component
weights: weights assigned to a component
[additional_values, …]: optional additional values, required to be paired with [additional_weights]
[additional_weights, …] : optional additional weights, required to be paired with [additional_values]
The Equation for Weighted Averages Explained:
Weighted Average = (Sum of Values * Weight of Values)/(Sum of Weights)
Sum of Values = Raw sum of all the Values
Weight of Values = (Weight) / (Sum of Weights)
Sum of Weights = Raw sum of weights (units, composition percentage, etc…)
Methods For Weighted Averages
Method 1 – Calculate using the AVERAGE.WEIGHTED function
The first method we will discuss in calculating Weighted Average in Google Sheets, is through the use of the AVERAGE.WEIGHTED function.
The AVERAGE.WEIGHTED function uses the same basic principle as the other methods we will discuss, but in a simpler manner.
You will need at least 2 ranges of the same dimension for this method.
Using the formula supplied below, replace “values” with the range that your values are stored, then replace “weights” with the range where your values’ weights are stored:
=AVERAGE.WEIGHTED(values, weights, [additional_values, …], [additional_weights, …])
You can add more value and weight pairings as optional values, provided that they are of the same dimensions, and are paired accordingly with each other.
For example, let’s assume that ranges A1:A20 & B1:B20 are your original “values” and “weights”, while A30:A35 & B30:B35 are your first additional pair, with D10:D25 & E10:E25 as your last additional pair.
The syntax for such a formula would then be:
=AVERAGE.WEIGHTED(A1:A20, B1:B20, A30:A35, B30:B35, D10:D25, E10:E25)
For example, let’s assume that you have the following values in the range A2:A6 and that you have the following weights in the range B2:B6:
| Grade | Unit |
| 3 | 1 |
| 4 | 2 |
| 2 | 3 |
| 1 | 3 |
| 1.75 | 6 |
You then have a formula that looks and works like this:
Method 2 – Calculate using the SUMPRODUCT & SUM formula
This method is more of an expansion of the calculations that the 1st method applies. It uses a combination of the SUMPRODUCT & SUM functions to arrive at the same results as the AVERAGE.WEIGHTED function does, using the same ranges, albeit using them a different number of times.
Using the formula supplied below, replace “values” with the range that your values are stored, then replace the 2 instances of “weights” with the range where your values’ weights are stored:
=SUMPRODUCT(values, weights)/SUM(weights)
Using the same table from Method 1, you would then have a formula that looks and works like this:
Method 3 – Calculate with the help of 2 new columns that compute weight and weighted values
The final method that we will discuss is the further breaking down of the 2 discussed methods into their most basic components: the weight of a value compared to its peers, and the value according to its weight.
For this method, please enter the following values into a table with 4 columns and 7 rows (columns 3 and 4 should remain blank for further use):
| Grade | Unit | Weight | Weighted Value |
| 3 | 1 | ||
| 4 | 2 | ||
| 2 | 3 | ||
| 1 | 3 | ||
| 1.75 | 6 | ||
| 2 | 5 |
At the top row of column 3, enter this formula after replacing ”weight” with the cell address of the weight value in the same row, then “weights” with the range of your weights, in absolute reference form, or column 2 from the example:
=[weight]/SUM([weights])
You should end up with a formula like the following, which you copy and paste to the remaining rows of the same column:
Next enter this formula at the top of column 4, by first replacing “value” with the cell address of the value from the same row, then replacing “weight_value” with the cell address of the result of the previous step:
=[value]*[weight_value]
You should end up with a formula like the following, which you copy and paste to the remaining rows of the same column:
Finally, you can add the values of your 4th column to arrive at your Weighted Average:
Weighted Average in Google Sheets examples
The weighted average of range A2:A15 according to their weight on range E2:E15:
=AVERAGE.WEIGHTED(A2:A15, E2:E15)
The weighted average of range A2:A15 according to their weight on range E2:E15, with additional values and weights on ranges A17:A22 and E17:E22, respectively:
=AVERAGE.WEIGHTED(A2:A15, E2:E15, A17:A22, E17:E22)
Weighted Average of range A2:A8 according to their weight on range A10:A16:
=AVERAGE.WEIGHTED(A2:A8, A10:A16)
Frequently Asked Questions On How To Calculate Weighted Average in Google Sheets
How do I use the AVERAGE.WEIGHTED function in Google Sheets?
Using the syntax, replace “values” with the range for your values, then “weights” with the range for your values’ weights, then paste onto a cell. Make sure that the dimensions for both ranges are equal and their contents have the same count.
=AVERAGE.WEIGHTED(values, weights)
How do I get rid of the #VALUE error when I use the AVERAGE.WEIGHTED function in Google Sheets?
There may be several factors to this. First, check if the dimensions of both your “values” and “weights” ranges are the same. Also check if they have equal the count of data across both ranges. If the “values” range has 21 items in it, the “weights” range must also have 21 items in it. Lastly, check if both ranges have any text values within them.
Will the AVERAGE.WEIGHTED function work for horizontally inclined tables in Google Sheets?
As long as the pairs of ranges are of the same inclination (including the optional ones), the AVERAGE.WEIGHTED function should work. (pair 1 can be vertical, pair 2 can be horizontal, pair 3 can be vertical, etc.)
Conclusion on Getting Weighted Average in Google Sheets
To get weighted averages in Google Sheets, use the formula =AVERAGE.WEIGHTED(“values”, “weights”) by replacing “values” with the range for your values, then by replacing “weights” with the range of the weights that correspond to your “values”.
You can add additional pairs of ranges using the syntax =AVERAGE.WEIGHTED(“values”, “weights”, “additional_values, …”, “additional_weights, …”).
Finally, enter your modified formula onto a blank cell.
