Table of Contents
A weighted average IF formula in Excel allows you to give different weights to different conditions in a single formula. This can be useful to provide a more accurate result when there are multiple possible scenarios. It works by multiplying each condition by its weight and then summing all the products together. The result is then divided by the sum of all the weights to give the weighted average IF result. This technique is useful to compare multiple scenarios with different weights and arrive at a single result.
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:
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 Excel.