How to Use a Weighted Average IF Formula in Excel


You can use the following syntax in Excel 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 Excel

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:

weighted average IF formula in Excel

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 Excel.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Calculate an Exponential Moving Average in Excel
How to Calculate a Weighted Moving Average in Excel
How to Calculate a Cumulative Average in Excel

Leave a Reply

Your email address will not be published.