Syntax=AVERAGE.WEIGHTED( values, weights, [additional values], [additional weights])
values: refers to the range of cells containing the data whose weighted average is to be found.
weights: refers to the range of cells comprising the weights assigned to each data value.
additional values: (optional) used when weighted average of more than one continuous series of data is to be calculated.
additional weights: (optional) used to assign weights when more than one continuous series of data is considered.
Sample Usage=AVERAGE.WEIGHTED( A2:A10, B2:B10)
//This calculates the weighted average of the values in the cell range A2:A10 with the weights being the data in the cell range B2:B10
Note
The values of the weights or additional weights cannot be negative. They can be 0, but one of the weights must be greater than 0. Each weight corresponds to a particular value, and each member of the values array must have a corresponding weight.
Sample Google Sheets template and formula here.
The weighted average is an average of a set of numbers that takes into account the degrees of weight or importance of the numbers in the dataset. A weighted average gives a more accurate picture of the set of data than a simple average in which all the numbers in the dataset are assigned an identical weight.
The weighted average method has a large number of uses and is used in different areas and fields. It is widely used in classrooms to grade courses based on the weight each subject or test carries; in statistical analysis, such as index numbers, for example, in calculating stock indexes; and in accounting; among others.
In this article, we’ll learn how to calculate weighted average in Google Sheets using two simple methods.
- Method 1: Using the WEIGHTED.AVERAGE function
- Example 1: Weighted average of students in a course
- Method 2: Using the SUMPRODUCT function
- Syntax of SUMPRODUCT
- Example 2: Weighted average of a share price
Method 1: Calculate weighted average in Google Sheets using the AVERAGE.WEIGHTED function
This is quite a straightforward method and it uses a very simple syntax to calculate the weighted average for a range of data.
Let us now learn how to calculate weighted average in Google Sheets using AVERAGE.WEIGHTED function.
Example 1: Calculating the weighted average of students in a course
We’ll take an example of a score of a particular high school student in a particular year, supposing the score for each subject along with its corresponding weight to be as given below:
To calculate weighted average in Google Sheets using the AVERAGE.WEIGHTED function, follow the steps given below.
- Choose an empty cell and enter the function to use, ie, =AVERAGE .WEIGHTED
Tip: You can press the Tab key to autocomplete the function, after Google Sheets has guessed the formula you’re typing in.
- Select the range of the values, which is the score in this case (C2:C9).
- Enter a comma and then select the values of the weight (D2:D9).
- Press Enter to complete the formula and display the result.
Method 2: Calculate weighted average in Google Sheets using the SUMPRODUCT function
This is a more familiar method as it is often how the weighted average is calculated when doing it without a dedicated function, such as on a piece of paper or with a simple calculator.
But it involves a few more steps than the AVERAGE.WEIGHTED function.
To calculate weighted average in Google Sheets using the SUMPRODUCT method, we calculate the sum of the product of the values and the weight and divide the result by the sum of the weight.
Formula for the SUMPRODUCT function
The generic syntax for the SUMPRODUCT function is given below:
=SUMPRODUCT(array1, [array2, ...])- The array1 parameter includes the numbers of the first series. For calculating the weighted average, they will constitute the values whose weighted average needs to be calculated.
- The array2 parameter contains the numbers from the second series. In this context, they will consist of the weights corresponding to each value.
Note: Only the first argument in the syntax is mandatory. A number of arrays may be added however for complex calculations.
Let us try with another example, again.
Example 2: Calculating the weighted average of a share price
This time, we’ll calculate the weighted average of the share price paid for shares of a company over time. Consider the data set as shown below:
Here, the number of shares is the value and the price of the shares is the weight.
The SUMPRODCT formula in this case is:
=SUMPRODUCT(values, weights)
To find the weighted average, we divide this by the sum of the weights.
The steps given below show how we can calculate weighted average in Google Sheets using the SUMPRODUCT function.
- Choose an empty cell in which you want to display the result.
- Type in the equal symbol (=) to begin the calculation and enter the SUMPRODUCT function. Then enter the range values and the weights. Close the function with parenthesis.
In this case, the range containing the number of shares are the values whose weighted average is to be calculated, and the price column is the weight. So the first part of the formula will be as shown below:
=SUMPRODUCT(B2:B6, C2:C6)
- Enter the forward slash (/) to divide the function and type formula for the sum of the weights–SUM(C2:C6)
- Press Enter on completing the formula.
The result will be displayed in the formula cell, as shown below:
Conclusion
Using the two methods discussed in this tutorial, we can find the weighted average for any number of applications, such as evaluating the performance of an employee where each task has a varying weight, ie, importance, or finding the weighted average of a list of goods with different qualities, to name just two.
Some commonly asked questions
What is a weighted average?
A weighted average or weighted mean is an average in which each value is given a specific weight, or importance, and based on the relative importance the average is calculated instead of treating all the individual items equally.
How is the weighted average different from the simple average?
In a simple average, all values have equal weight and thus their relative importance or significance is overlooked giving a distorted average. The weighted average avoids this by giving more weight or emphasis to those values that are more crucial giving a more accurate picture of the data set. A weighted mean also minimises the impact of outliers on the average which can drastically influence the average.
Why is the weighted average used?
A weighted average is used to assign different significance to different types of data. Treating each of the data alike, as in a simple average, can give a distorted average making it less useful. The weighted average is also used to mitigate the effect of outliers on the average.
See also
Using built-in functions to calculate weighted average in Google Sheets is one of the various things you can do to simplify and speed up your tasks. We have a number of such formulas and functions on our blog that you must know if you are looking for ways to minimise your time on tabulation and help you with data proficiency.
To check them out, click here.
Here are some articles you might find useful:
https://blog.tryamigo.com/averageifs-function-in-google-sheets-easy-guide/
https://blog.tryamigo.com/simple-moving-average-in-google-sheets/
https://blog.tryamigo.com/mean-median-and-mode-in-google-sheets/