Calculating the average is a common task for people working with data in Google Sheets.
And when quantities in a dataset don’t carry the same amount of importance, finding simple arithmetic mean doesn’t really suffice. In such cases, weighted arithmetic mean can be a better representation of the data.
In this weighted average Google Sheets tutorial, we will show you two easy ways in which you can calculate the weighted average in Google Sheets:
- Using the SUMPRODUCT function
- Using the AVERAGE.WEIGHTED function
So let’s get started!
Table of Contents
What is the Weighted Average?
The weighted average is an arithmetic mean calculated while taking into account the importance of elements in the data.
In this way, it gives a more accurate picture of the data than regular arithmetic mean.
The weighted average is often used in analyzing class performance, accounting, and statistical analytic operations.
When to Use Weighted Average
Let us take a look at an example.
A student may take three different tests (say, a class test, a mid-term, and a final).
Here, each test carries a different amount of importance or contribution to the final grade. The class test is less important, the mid-term carries a little more importance, while the final exam carries the most importance.
In such cases, simply summing up the scores and dividing by 3 would not take into consideration the importance (or weightage) of each exam.
Therefore, it will not give an accurate representation of the student’s performance.
to calculate the weighted average if the scores are weighted as above, on the other hand, it would take into account the weightage of the individual tests. You calculate it by:
- Multiplying each test score with its corresponding weightage.
- Adding up each of these products
- Dividing this sum by the sum of all the weights
So in this student’s case, the weighted average is calculated as follows:
Weighted average = [(75 x 20) + (80 x 30) + (60 x 50)] / (20 + 30 +50) = 6900 / 100 = 69
This value gives a much more accurate picture of how well the student performed since it didn’t just consider the scores in individual tests, but also the importance of each score.
How to Calculate Weighted Average In Google Sheets
Google sheets have a few good functions that can help you calculate the weighted average of values in your data:
- THE SUMPRODUCT function
- The AVERAGE.WEIGHTED function
We will take a look at how to use these two functions individually. For both methods, we will use the following data set:
How to Weight Grades in Google Sheets Using the SUMPRODUCT Function
The first method involves the use of the Google Sheets SUMPRODUCT function.
The SUMPRODUCT function lets you find the sum of products of a set of variable values.
Syntax of the SUMPRODUCT Function
The syntax for the SUMPRODUCT function is as follows:
SUMPRODUCT(array1, array2, ....)
Here, array1, array2, array3, etc. are separate variables. This could be a range of cells, a list of values, or an individual column name.
For example, if we have two ranges A1:A5 and B1:B5, the function: SUMPRODUCT(A1:A5, B1:B5) will take each value of the first range (A1:A5) and multiply it with the corresponding value in the second range (B1:B5).
It will then add up all these individual products to give the final result.
So if you have the below formula
=SUMPRODUCT(A1:A5, B1:B5)
this is what it does in the backend in Google Sheets
= (A1 * B1) + (A2 * B2) + (A3 * B3) + (A4 * B4) + (A5 * B5)
The SUMPRODUCT function is great for finding the weighted average since a large part of the calculation involves finding the sum of products.
To apply it to finding the weighted average, you can specify the range containing the individual data values as array1 and the range containing the weights as array2.
You can then divide the result by the sum of the weights!
How to Do Weighted Average in Google Sheets Using SUMPRODUCT to Find the Weighted Average of Grades
In our example, we want to find the products of the individual test scores with their corresponding weightage, sum up all the products and then divide this sum by the sum of the weights.
Here are the steps you need to follow to apply the SUMPRODUCT function to the above example:
- Select the cell where you want to display the weighted average (C8 in our example).
- Type in the formula:
=SUMPRODUCT(B2:B7, C2:C7) / SUM(C2:C7).
- Press the Return key.
You should see the resultant weighted average in your selected cell.
Finding the Weighted Average using AVERAGE.WEIGHTED Google Sheets function
AVERAGE.WEIGHTED is the weighted average formula in Google Sheets.
You will not find a function like this in Excel that is dedicated solely to the purpose of finding the weighted average.
This function makes it much simpler to calculate the weighted average, compared to the SUMPRODUCT method.
Syntax of the AVERAGE.WEIGHTED Function
The syntax for the AVERAGE.WEIGHTED function is as follows:
AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights])
Here,
- values are the data values you want to find the weighted average for
- weights is the range of cells containing the corresponding weights.
As you can see from the syntax, it is also possible to use multiple sets of data values and their corresponding sets of weights.
The AVERAGE.WEIGHTED function makes the calculation of weighted average much easier than SUMPRODUCT, since you only need to specify the ranges for the values and weights, without having to perform any subsequent operations.
For example, if we have a set of values in the range A1:A5 and corresponding weights in B1:B5, the function: AVERAGE.WEIGHTED(A1:A5, B1:B5) will take each value of the first range (A1:A5) and multiply it with the corresponding value in the second range (B1:B5).
It will then add up all these individual products. Finally, it will divide this final sum by the sum of the weights to give the final result.
So, if you have a formula as shown below:
=AVERAGE.WEIGHTED(A1:A5, B1:B5)
this is what it does in the backend in Google Sheets
= [(A1 * B1) + (A2 * B2) + (A3 * B3) + (A4 * B4) + (A5 * B5)] / (B1+ B2 + B3 + B4 + B5)
Using AVERAGE.WEIGHTED Function to Find Weighted Average
In our example, we can directly use the AVERAGE.WEIGHTED function without having to use it in combination with any other function.
Here are the steps you need to follow to apply the AVERAGE.WEIGHTEDfunction to the above example:
- Select the cell where you want to display the weighted average (C8 in our example).
- Type in the formula: =AVERAGE.WEIGHTED(B2:B7,C2:C7).
- Press the Return key.
You should see the resultant weighted average in your selected cell.
Notice the simplicity of the formula. All we are using is a range of values and a range of weights.
When Should You Not Use AVERAGE.WEIGHTED in Google Sheets?
- You cannot use this function when data from the corresponding column is missing
- Values in each column also need to be the same type of data (numbers, dates, etc)
Weighted Average Google Sheets FAQ
How Do You Calculate Weighted Average in Google Sheets?
You can use the AVERAGE.WEIGHTED function that uses the following syntax:
=AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights])
You use cell ranges for the values and weights. The weight column must correspond to the values and is usually expressed as a percentage.
Is Google Sheets Weighted Average the Same as SUMPRODUCT?
Both functions can calculate weighted average, but they operate under different syntax. AVERAGE.WEIGHTEDÂ is exclusive to Google Sheets and won’t work in Excel.
What Is the Difference Between Average and Weighted Average?
Weighted average pre-defines the relative importance of each data point. On the other hand, average just takes the overall mean from the entire dataset.
When Should I Use a Weighted Average?
You should use weighted averages when assigning more significance to certain data points. For example, a teacher may consider one assignment more significant than others when deciding overall grades. Alternatively, you could also use a moving average.
Wrapping Up the Weighted Average Guide
In this weighted average Google Sheets tutorial, I showed you two ways to calculate the weighted average.
One using the generic SUMPRODUCT function, and another using a special Google Sheets function, WEIGHTED.AVERAGE.
The latter function is specific to Google Sheets and is dedicated specifically to calculating the weighted average so it won’t translate to Excel if you need it to.
I hope this tutorial was helpful to you.
Other Google Sheets tutorials you may like:
