Table of Contents
To use a weighted average IF formula in Excel, first select the cell where you want the result to appear. Then, enter the formula “=SUMPRODUCT(range1,range2)/SUM(range2)”, replacing ‘range1’ with the data range you want to multiply and ‘range2’ with the data range you want to use as weights. Finally, add the criteria for the IF statement within the SUMPRODUCT function. This formula calculates the weighted average of a set of values based on specified criteria.
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:
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How do I use a weighted average IF formula in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-use-a-weighted-average-if-formula-in-excel/
stats writer. "How do I use a weighted average IF formula in Excel?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-use-a-weighted-average-if-formula-in-excel/.
stats writer. "How do I use a weighted average IF formula in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-use-a-weighted-average-if-formula-in-excel/.
stats writer (2024) 'How do I use a weighted average IF formula in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-use-a-weighted-average-if-formula-in-excel/.
[1] stats writer, "How do I use a weighted average IF formula in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I use a weighted average IF formula in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.