How to Calculate Weighted Average in Google Sheets (Easy Formula)

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 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!

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.

Practical Implementation of the Weighted Average

Let us take 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.

Class test score for weighted average

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.

Simple arithmetic mean

If we calculate the weighted average of the scores, on the other hand, it would take into account the weightage of the individual tests. You calculate it by:

  1. Multiplying each test score with its corresponding weightage.
  2. Adding up each of these products
  3. 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.

Weighted average of the scores

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:

Dataset for calculating weighted average in google sheets

Finding the Weighted Average using SUMPRODUCT function in Google Sheets

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!

Using SUMPRODUCT Function to Find Weighted Average

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:

  1. Select the cell where you want to display the weighted average (C8 in our example).
  2. Type in the formula:
    =SUMPRODUCT(B2:B7, C2:C7) / SUM(C2:C7).
  3. Press the Return key.

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

SUMPRODUCT function to calculate weighted average

Finding the Weighted Average using AVERAGE.WEIGHTED function in Google Sheets

The AVERAGE.WEIGHTED function is one function that you will get only on 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:

  1. Select the cell where you want to display the weighted average (C8 in our example).
  2. Type in the formula: =AVERAGE.WEIGHTED(B2:B7,C2:C7).
  3. Press the Return key.

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

Average Weighted to calculated weighted average

Notice the simplicity of the formula. All we are using is a range of values and a range of weights.

Note: It is important to have all cells filled with a numeric value when using the AVERAGE.WEIGHTED function. If you have a cell left blank, then the formula returns an error. So, make sure you fill all blank cells with at least a ‘0’. There is no such usage barrier when using the SUMPRODUCT function, though.

In this tutorial, I showed you two ways to calculate the weighted average in Google Sheets.

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.

I hope this tutorial was helpful to you.

Other Google Sheets tutorials you may like:

Leave a Comment