How to Calculate Weighted Average In Google Sheets

Learning how to calculate weighted average in Google Sheets is useful in providing a better representation of the data when some numbers in the data set are more important than the others.

In this article, we’ll be learning more than one way of obtaining the weighted average of a given data set. We shall start with manual calculations to better grasp the concept of weighted average. Next, two different functions – SUMPRODUCT and AVERAGE.WEIGHTED – shall be used to obtain the same results with less effort.

 

What is a Weighted Average?

First, let us first clarify what weighted average means and how it differs from the average value that most of us are familiar with.

A simple average is obtained by taking the sum of the numbers in the data set and dividing this amount by how many numbers are present. In this case, all the numbers are treated with equal importance in obtaining the average value. There may be cases, however, where some factors are more important or carry more “weight” compared to the rest. For such cases, a weighted average will give a more accurate value. But what does it mean for factors to be more important than others?

Consider this example.

You decided to conduct a survey to determine how many movies an average person watches in a month. The results of your survey are as follows:

Of your 40 respondents, 9 people said that they normally don’t watch movies, 13 people only watched one, and so on. You take note, however, of one response in which one of the respondents said that they watch one movie a day, which counts for an average of 30 movies per month. Simply taking the average of the values in the number row results in an average of 6.375. This is an absurd conclusion as only one of the respondents watches more than 6 movies a month. You decided to remove the person who answered 30 from the calculations and ruled them out as an outlier.

You take the average of the values in the number row again, this time removing the 30 value, and obtain an average of 3 movies in a month. This seems like a more plausible conclusion; however, you notice that this may also not be accurate as more than half of the respondents answered that they only watch one or none at all. Surely enough, values in the frequency row should play a part in calculating the average value, but you have no idea how to incorporate this value in your calculations.

This is where the concept of a weighted average could be applied. Values that have a higher frequency are more important or have more “weight” in the calculation of the average value, while those that have a lower frequency are less important. Simple averages consider all numbers in the data set to be equally important or have equal weights.

Now that we know what a weighted average is and when it is used, let’s finally learn how to calculate the weighted average value!

 

How to Calculate Weighted Average Manually in Google Sheets

The weighted average of a given data set is calculated by multiplying the numbers to be averaged to their corresponding weights and dividing this value by the sum of the weights. The concept behind this is by treating the weights as important factors. Instead of converting the weights to percentages (dividing each by the total number of weights), the weights are directly multiplied to the values to be averaged. Dividing by the total number of weights is factored out and then multiplied to the sum of those products.

Let’s try to apply this to the example earlier.

  1. First, let’s convert the table such that instead of the number and frequency being presented in a row, it’s presented in a column. The first step in calculating the weighted average is by multiplying the numbers column (values to be averaged) to the frequency column (weights). Let’s make another column to place these products.

 

  1. Click on any cell to make it the active cell. For this guide, I will be selecting C2, where I want to show my result. To multiply the values in the number column to their frequencies, simply type the equal sign ‘=’ to begin the formula, click on cell A2, type ‘*’, then click on B2. Press the Enter key to finish the formula. Copy this formula to the remaining values.

 

  1. Next, the sum of these products needs to be obtained. Using the SUM function, place this value in cell C10. Since the total of the frequencies is also needed, place the sum in cell B10.

 

  1. To obtain the weighted average, simply obtain the quotient of these two values: divide the sum of the products (C10) by the total frequency (B10). Alternatively, the previous step may be skipped by directly inputting the SUM function in the formula, as shown in cell C13.

 

You’re done! Simple, right? Notice that the value obtained using a weighted average is closer to the value with the highest frequency as compared to the simple average.

Now, let me teach a faster and simpler way to calculate the weighted average – by using the SUMPRODUCT Function.

 

The Anatomy of the SUMPRODUCT Function

So the syntax (the way we write) the SUMPRODUCT function is as follows:

=SUMPRODUCT(array1, [array2, ...])

Let’s dissect this thing and understand what each of these terms means:

  • = the equal sign is just how we start any function in Google Sheets.
  • SUMPRODUCT() this is our SUMPRODUCT function. It calculates the sum of products of corresponding entries of equally sized arrays.
  • array1 is the first array or range whose entries will be multiplied with corresponding entries in the other arrays or ranges.
  • array2, ... is the second array or range whose entries will be multiplied with corresponding entries in the first array or range. The square brackets ‘[]’ indicate that this is an optional parameter. More than one array or range may be inputted.

 

How to Calculate Weighted Average Using the SUMPRODUCT Function

From its name, the SUMPRODUCT function takes the sum of the products of two or more arrays or ranges. When used for calculating the weighted average of a data set, it eliminates the steps of manually calculating the products of each term with their weight and taking their sum.

Applying this function to the example earlier:

  1. Click on any cell to make it the active cell. For this guide, I will be selecting C15 where I want to show my result. Next, simply type the equal sign ‘=’ to begin the function and then followed by the name of the function, which is our ‘sumproduct’ (or ’SUMPRODUCT’, whichever works).

 

  1. Select the range corresponding to the first array. Either the values in the numbers column or frequency column may be the first array, as terms in a product may be interchanged without affecting the result. For this example, I will select the numbers column (A2:A9) as the first array, and the frequency column (B2:B9) as the second array. Press the Enter key to finish the formula.

 

  1. To obtain the weighted average, simply divide this value by the total frequency. As shown in cell C16, I inputted both the SUM and SUMPRODUCT function in the formula to illustrate how the weighted average may be directly computed.

The SUMPRODUCT function is a diverse function that may be used in various situations. However, another function provides the easiest and fastest way in calculating the weighted average – the AVERAGE.WEIGHTED function.

The Anatomy of the AVERAGE.WEIGHTED Function

So the syntax (the way we write) the AVERAGE.WEIGHTED function is as follows:

=AVERAGE.WEIGHTED(values, weights, [additional_values], [additional_weights])

Let’s dissect this thing and understand what each of these terms means:

  • = the equal sign is just how we start any function in Google Sheets.
  • AVERAGE.WEIGHTED() this is our AVERAGE.WEIGHTED function. It multiplies the values with the corresponding list of weights, takes their sum, and then divides this total with the sum of weights.
  • values is the list of values to be averaged. A cell reference, range of cells, or the values themselves may be used.
  • weights is the corresponding list of weights to be applied. A cell reference, range of cells, or the weights themselves may be used. Negative values are not allowed, but zero is permitted. If a range is used, it must be of the same size (same number of rows and columns) as the range of values.
  • additional_values provides another set of values to be averaged. The square brackets ‘[]’ indicate that this is an optional parameter. The same rule of having a cell reference, range of cells, or the values themselves in this argument is applied.
  • additional_weights provides the corresponding list of weights to be applied for additional_values. The square brackets ‘[]’ indicate that this is an optional parameter. The same rules for weights are applied. If a range is used, it must be of the same size as the range of additional_weights.

You can find out more about the AVERAGE.WEIGHTED Function in our article, ‘How to Use AVERAGE.WEIGHTED Function in Google Sheets.

 

How to Calculate Weighted Average Using the AVERAGE.WEIGHTED  Function

The AVERAGE.WEIGHTED function provides the most straight-forward approach in solving for the weighted average of a data set.

Looking at the example earlier:

  1. Click on any cell to make it the active cell. For this guide, I will be selecting C18 where I want to show my result. Next, simply type the equal sign ‘=’ to begin the function and then followed by the name of the function, which is our ‘average.weighted’ (or ’AVERAGE.WEIGHTED’, whichever works).

 

  1. Next, select the values to be averaged. For this function, the order of the arrays inputted in the function is important, so be careful of interchanging the values. Select the numbers column (A2:A9) as the first array and the frequency column (B2:B9) as the second array. Press the Enter key to finish the formula.

You’re done! So quick and easy right? Now you know how to calculate the weighted average of a data set using three different methods: manual calculation, using SUMPRODUCT, or AVERAGE.WEIGHTED function! Personally, I would recommend using the AVERAGE.WEIGHTED function as it substantially cuts down the work you have to do.

If you want to practice some more, make a copy of our spreadsheet and give it a try:

Or browse our other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like