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
We can plug in the values from our dataset into this formula to calculate the weighted average exam score for student A:
- 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.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: How to Calculate Average by Group
Google Sheets: Calculate Average Excluding Outliers
Google Sheets: Calculate Average If Between Two Dates