Table of Contents
Google Sheets can be used to calculate a weighted average using the IF formula. This formula allows users to assign different weights to different values in a data set and then calculate the weighted average based on those weights. This is particularly useful for situations where the relative importance of different data points needs to be taken into account when calculating an average. For example, if a user wanted to calculate an average grade that takes into account different weights for different assignments, this formula can be used to do so.
You can use the following syntax in Google Sheets to apply a weighted average IF formula:
=SUMPRODUCT(--(A2:A7="A"), B2:B7, C2:C7)/SUMIF(A2:A7, "A", C2:C7)
This formula calculates the weighted average of the values in the range B2:B7, using C2:C7 as the weights, only for the cells where A2:A7 are equal to “A”.
The following example shows how to use this formula in practice.
Example: Weighted Average IF Formula in Google Sheets
First, let’s enter the following data that shows the scores for two students (Student A and Student B) on three different exams:
Next, we’ll use the following formula to calculate the weighted average of exam scores for student A only:
=SUMPRODUCT(--(A2:A7="A"), B2:B7, C2:C7)/SUMIF(A2:A7, "A", C2:C7)
The following screenshot shows how to use this formula in practice:
The weighted average of exam scores for student A is 78.
We can verify this is correct by manually computing the weighted average exam score for student A.
Recall that we use the following formula for weighed average:
Weighed Average = ΣwiXi / Σwi
where:
- wi = the weight values
- Xi = the data values
- Weighed Average for Student A = ΣwiXi / Σwi
- Weighed Average for Student A = (2*60 + 5*90 + 70*3) / (2+5+3)
- Weighed Average for Student A = 78
This matches the value that we calculated using the formula in Google Sheets.