Understanding how to use Google Sheets to do your calculations can greatly speed up the time it takes you to do various tasks.
One common calculation that many do in spreadsheet programs is calculating weighted averages. There are a couple of ways to do this calculation in Google Sheets.
In this tutorial, I will show you how to calculate weighted average in Google Sheets.
Table of Contents
What is Weighted Average?
A weighted average also called weighted mean is an average where some values contribute more than others.
In a normal average, all of the numbers use an equal weight and are counted equally.
With a weighted average, some numbers might carry more weight than others.
In practice, weighted averages are often used in calculating grades for class performance, accounting, statistics, and analyzing stock performance.
To help you understand weighted averages better, let me explain a real-life situation where weighted averages are used.
Weighted averages are often used by schools to calculate GPA (Grade point average).
Higher-level courses carry more weight than lower-level courses. So a higher-level course may carry a “weight” of 1.05 towards a GPA, while a lower-level course may only carry a “weight” of 1.0.
In situations like this, using a weighted average is sometimes seen as more accurate than a standard average.
How to Calculate Weighted Average
Now that we’ve gone over the basics of what a weighted average is, let’s take a look at how to calculate it in your spreadsheet.
There are a number of ways to do this in Google Sheets, but two of the best methods are:
- Using the AVERAGE.WEIGHTED function
- Using the SUMPRODUCT function
I will cover how to calculate using each method below.
Calculate Weighted Average Using the AVERAGE.WEIGHTED Function
Google Sheets has a built-in function called AVERAGE.WEIGHTED to calculate weighted averages. This is by far the easier method for calculating this in your spreadsheet.
The AVERAGE.WEIGHTED function will find the weighted average of a set of values when given the values and their weights.
The syntax of this function is:
=AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights])
- Values – these are the values that you want to be averaged. This can be a range of cells, or values entered themselves
- Weights – this is the list of weights to apply to the values
- Weights can be a range of cells or the weights entered themselves
- These cannot be negative (they can be zero)
- At least one need to be positive
- If a range of cells is used it has to have the same number of rows and columns as the values
- Additional values – this argument is optional. If you want additional values averaged you can place them here
- Additional weights – this argument is optional and dependent on additional values. Each additional value should be followed by 1 additional weight.
To show you how to use this function in your spreadsheet, I will calculate the weighted average using this example data:
In this example, I have a student’s grades for each course and the weights of each course.
To calculate the weighted average grade for this student with this example data using the WEIGHTED.AVERAGE function, I would set up my formula like this:
=AVERAGE.WEIGHTED(B2:B6,C2:C6)
In the example above, the range of B2:B6 contains my values (the first argument of my function. The range of C2:C6 contains the weights (the second argument of my function). As we covered earlier it’s important that these two arguments have the same number of rows and columns for the function to work properly.
Now let’s breakdown how I set up the above function step by step:
1. In the cell where you want to calculate the weighted average type =AVERAGE.WEIGHTED and press then Tab on your keyboard
2. Next we need to add the first argument in our function which is the values that we want to average. In this example I want to average the grades, so I have selected the range of cells from B2:B6. Add a comma (,) when you are done with this step
3. Next we need to add the second argument of our function. This is the weight that each value should carry. In my example data, the weight is in cell range C2:C6. Add a closing parenthesis when you are done with this step “)”
4. Press Enter on your keyboard and your weighted average will calculate for the data you have entered
You can see that using this function makes finding weighted averages incredibly easy. You can simply set up a formula and find your weighted average immediately.
This is much quicker than calculating everything manually. Google Sheets makes this incredibly easy.
Calculate Weighted Average Using the SUMPRODUCT Function
If you are familiar with the SUMPRODUCT function in Google Sheets, this is another easy way to calculate the weighted average in your spreadsheet.
The SUMPRODUCT function calculates the sum of products from 2 ranges or arrays of the same size.
The syntax of the SUMPRODUCT function is:
=SUMPRODUCT(array1, [array2, …])
- array1 – this is the first range or array to be multiplied with the corresponding data in the second range or array
- array2 – this is the second range or array to be multiplied with the corresponding data in the first range or array. This argument is optional
To get a better understanding of how SUMPRODUCT works, let’s breakdown this formula:
=SUMPRODUCT(A1:A3, B1:B3)
SUMPRODUCT will take the sum of the products for the range of cells that I have entered as arguments into the function.
What Google Sheets will do in this example formula is:
=(A1 * B1) + (A2 * B2) + (A3 * B3)
You can now see how this can be useful to calculate the weighted average. To do this we will be using SUMPRODUCT with our values and weights and dividing it by the sum of the weights.
Here is what the formula will look like using SUMPRODUCT to find the weighted average:
=SUMPRODUCT(values,weights)/SUM(weights)
Here is the exact formula using our previous data
The formula in my example data is =SUMPRODUCT(B2:B6, C2:C6)/SUM(C2:C6). You can see that the final result is the same as with the previous method.
Closing Thoughts
We have now shown two different ways to calculate the weighted average inside your Google Spreadsheet.
Using the AVERAGE.WEIGHTED function is the easier method, but learning how to do the calculation with SUMPRODUCT may certainly come in handy somewhere down the line too.
More Google Sheets Tutorials:
How to Calculate Percentage
How to Calculate p-Value
How to Use the IRR Function
How to Use the SUBTOTAL Function
How to Make a Gradebook Template