How to do a weighted average in Google Sheets
In this tutorial, we will learn to calculate Weighted Average. Weighted Average in Google Sheets is a function that calculates the average of a set of values, where each value has a specific weight attached to it. The weighted average is calculated by multiplying each value by its corresponding weight, summing the results, and then dividing by the sum of the weights.
We have a dataset that includes the test marks and their corresponding weightage for a university student’s results. We aim to calculate the weighted average of these results to determine the overall total result. To accomplish this, we have two methods available.
Method 1: Calculate the weighted average using the WeightedAverage function
Step 1 – Select the cell
- Click on the cell, where you want to show the result
Step 2 – use the Weighted Average function
- After selecting the cell, type “=Weighted Average(” to use the Weighted Average function
Step 3 – Type the Arguments of the function
- After typing the function, type the arguments as follow:
- data_range: The range of cells containing the data to be averaged. Here it is B2:B6
- weights_range: The range of cells containing the weights to be applied to each corresponding cell in the data_range.Here it is C2:C6
Step 4 – Press the Enter key
- After typing the arguments, press the Enter key to get the required result
Method 2: Calculate the weighted average using the SUMPRODUCT function
Step 1 – Select the cell
- Click on the cell, where you want to show the result
Step 2 – use the SUMPRODUCT function
- After selecting the cell, type “=SUMPRODUCT(” to use the Weighted Average function
Step 3 – Type the Arguments of the function
- After typing the function, type the arguments as follow:
- array1: The first array or range to be multiplied and summed. Here it is B2:B6
- array2: Additional arrays or ranges to be multiplied and summed. Here it is C2:C6
Step 4 – Press the Enter key
- After typing the arguments, press the Enter key to get the required result