How to Calculate Weighted Average in Google Sheets

Imagine a world where students’ grades aren’t based on their raw scores but on how important each assignment was. Well, that’s what makes weighted averages so unique. Welcome to the world of weighted averages, where a single project or test does not have the same clout or influence over a student’s overall score as a group of smaller projects or assignments.

In today’s guide, we will focus on showing you how to calculate weighted average in Google Sheets. Whether this is your first time or you’ve dabbled in the past, you can be confident that today’s guide will provide you with a thorough understanding of calculating weighted averages in Google Sheets.

Before we get into all the exciting stuff, let’s look at what a weighted average means.

What is the weighted average?

Are you just hearing about the weighted average and wondering what it means? Well, here you go.

A weighted average is an arithmetic mean. It is calculated while factoring in the importance of elements in the data.

By default, the weighted average is designed to give a better and more accurate picture of data than the typical arithmetic mean.

The weighted average has many use cases, from analyzing class performance to resolving accounting issues and performing other statistical analytic operations.

As we delve further, we will show you how to calculate weighted average in Google Sheets.

Making a case for the weighted average

We can’t say enough about how important the weighted average is when figuring out how well a class did. Let’s look at this example to give you more insight into what we mean.

Let’s assume a student takes three different tests (One for a class test, the other for midterm, and the last one for the final exam)

Let us also assume that each test ranks in terms of importance, i.e., the class test is less important, the mid-term test carries more weight, and the final exam ranks as the most important.

How to Calculate Weighted Average in Google Sheets

In this scenario, simply summing up all the scores and dividing them by three will not paint the picture we want, as it will not consider each exam’s importance. To this end, the final result won’t accurately depict the student’s performance.

To calculate the weighted average for our example above, we need to consider the weight of each test in terms of importance. To do this:

We need to multiply each test score by its corresponding weightage
We need to then add up each of the products
And finally, divide the result by the sum of all weights.

So as per our example above, the weighted average will look thus:

Weighted average = [(79 x 20) + (82 x 30) + (65 x 50)] / (20 + 30 +50)
= 7280/100
= 72.8

What we have above gives a clearer picture of how the student performed, especially considering that it didn’t just consider the scores of individual tests, but also the importance of each score.

Note: The example we just considered is just to give you some insight into how weighted averages work. So it is entirely different from the step-by-step example we will show you shortly.

How to calculate weighted average in Google Sheets

Open the Example Sheet

Follow Along With Our Weighted Average Example

If you don’t have data to work with, click the above button to open our sample data in Google sheets. Once it is open, go to File > Make a copy to create a duplicate for your own use.

Now that you understand what weighted average is, let’s show you some practical steps on how to calculate weighted average in Google Sheets.

When it comes to calculating weighted averages using Google Sheets, there are two formulas we can use to get the job done. They include:

THE SUMPRODUCT function
The AVERAGE.WEIGHTED function

Our guide will show you how to use both of these functions individually. We will use the following sample data for each method to calculate weighted averages in Google Sheets.

Calculating weighted average in Google Sheets using the SUMPRODUCT function

As we stated earlier, we will show you two ways of calculating weighted averages in Google Sheets. The first method involves using the SUMPRODUCT function. This product allows you to find the sum of products of a specific set of variable values.

For this example, our objective is to find the products of the individual test scores together with the corresponding weightage. Finally, we would sum up all the products and divide this by the sum of the weights.

Here is a breakdown of the steps you need to follow to apply the SUMPRODUCT function to the sample data we provided earlier.

Start by highlighting the cell where you want to display the weighted average. In our example, we decided to use cell C8.
Now, head to the formula bar and type in the following formula:

=SUMPRODUCT(B2:B7, C2:C7) / SUM(C2:C7).

With this formula typed into the formula bar, you can proceed to press the Enter button or Return key if you are using a MAC device.

You should see the weighted average entered in your selected cell.

If you followed this step exactly as we highlighted above, your spreadsheet should look something like this:

How to Calculate Weighted Average in Google Sheets using SUPRODUCT function

Calculating weighted average using the AVERAGE.WEIGHTED function

In the method above, we talked about how to use the SUMPRODUCT function in Google Sheets to find the weighted average. Now, we want to show you the second method using AVERAGE.WEIGHTED function.

What we love about this method is how easy it makes the entire process. But don’t just take our worlds for it. Check it out for yourself by following the step-by-step guide below.

Start by selecting the cell where you want to show the result of the weighted average. As we did in the first method, we will be using cell C8 to show our result.

The next thing you want to do is head to the formula bar and type in the following formula:

=AVERAGE.WEIGHTED (B2:B7, C2:C7)

Finally, hit the Enter or Return key if you use a MAC device.

You should see the weighted average nicely showcased in the selected cell.

If you followed all the steps highlighted above correctly, your spreadsheet should look like this:

How to Calculate Weighted Average in Google Sheets isomg AVERAGe.WEIGHTED function

If there is anything we love about the AVERAGE.WEIGHTED function is its simplicity. Compared to the SUMPRODUCT function, this method only factors in the range of values along with the range of weights to arrive at the final answer.

So if you’re looking for an easier method of calculating weighted averages in Google Sheets, we would highly recommend going with the AVERAGE.WEIGHTED function.

While the AVERAGE.WEIGHTED method makes your job easy; we want to mention some important things you should know when using this method.

To have a smooth experience using the AVERAGE.WEIGHTED method, you must ensure that all cells have a numeric value. If, for instance, you have a blank cell, applying the AVERAGE.WEIGHTED formula will return an error. To this end, you want to ensure that all blank cells have a value, even if it’s just 0.

On the flip side, when using the SUMPRODUCT function, you don’t have such a barrier.

Instances when you shouldn’t use the AVERAGE.WEIGHTED method to calculate weighted average in Google Sheets

There are several instances when you shouldn’t use the AVERAGE.WEIGHTED method to calculate weighted average in Google Sheets. Here, check out two of them:

When you have a column or cell missing data, the AVERAGE.WEIGHTED formula is a no-no as it would return an error.

When using the AVERAGE.WEIGHTED function, it’s important that values in each column are of the same type of data (dates, numbers, etc.).

Conclusion

If you have struggled to calculate weighted average in Google Sheets before, we are sure following our guide has made the entire process easier. More so, we provided you with two methods you can use to calculate weighted average in Google Sheets.

If you’re looking for simplicity, we highly recommend using the AVERAGE.WEIGHTED function. However, if you don’t mind spending a few extra minutes calculating weighted averages in Google Sheets, you are free to use the SUMPRODUCT method.

Keep in mind that whichever method you decide to use will give you the same answer. The only difference between both methods is the simplicity you get with one method over the other.

Frequently asked questions

What is a weighted average?

A weighted average or weighted mean, as it is also called, is a type of average where each value is given specific importance. So based on the relative importance of each data, an average is calculated. The weighted average gives a better picture of the data you’re looking at in terms of importance.

Which should I use to calculate weighted average in Google Sheets: The AVERAGE.WEIGHTED formula or SUMPRODUCT method?

To be honest, there is no right or wrong answer here, as it all boils down to preference. If you’re looking for a method that makes the entire process easy, we suggest you go with the AVERAGE.WEIGHTED method.

On the flip side, if you don’t mind spending a few extra minutes on your project, you can use the SUMPRODUCT method.

At the end of the day, keep in mind that you’ll get the same result regardless of the method you decide to use.

Why is the weighted average an important metric in data analysis?

The weighted average is an important metric in data analysis, and that’s because it assigns importance to different types of data. Instead of treating each piece of data as the same, just like its obtainable when using simple averages, the weighted average analysis data from the standpoint of importance.