Average score by course with AVERAGEIF
BeginnerWhen you have results spread across multiple categories, AVERAGEIF lets you calculate an average for each category without sorting or filtering your data. It works just like SUMIF and COUNTIF, but instead of summing or counting, it averages.
In this exercise, you have a table of student exam results in rows 2–10. Each row shows a student's name, their course, and their score. Below the table is a summary section where you'll calculate the average score for each course.
How AVERAGEIF works:
=AVERAGEIF(range, criteria, average_range)
- range: the column to check for a match (the Course column)
- criteria: the value to match (the course name)
- average_range: the column to average when a match is found (the Score column)
What you need to do:
- Click cell B13 (next to "Math").
- Type an AVERAGEIF formula that looks through the Course column (B2:B10) for entries matching the label in A13, and averages the corresponding scores from C2:C10.
- Press Enter.
- Copy the formula from B13 down to B14 and B15.
When you're done, each row in the summary table should show the correct average score for that course.
Need some help?
Hint 1
Watch out: if you leave out the third argument (average_range), Excel will average the Course column instead of the scores and you'll get an error. All three arguments are required for AVERAGEIF to work correctly.
Hint 2
Lock the range and average_range with dollar signs ($B$2:$B$10 and $C$2:$C$10) so those references stay fixed when you copy the formula down. The criteria cell (A13, A14, A15) should stay relative so it shifts with each row.
Related function(s)
Average score by course with AVERAGEIF
BeginnerWhen you have results spread across multiple categories, AVERAGEIF lets you calculate an average for each category without sorting or filtering your data. It works just like SUMIF and COUNTIF, but instead of summing or counting, it averages.
In this exercise, you have a table of student exam results in rows 2–10. Each row shows a student's name, their course, and their score. Below the table is a summary section where you'll calculate the average score for each course.
How AVERAGEIF works:
=AVERAGEIF(range, criteria, average_range)
- range: the column to check for a match (the Course column)
- criteria: the value to match (the course name)
- average_range: the column to average when a match is found (the Score column)
What you need to do:
- Click cell B13 (next to "Math").
- Type an AVERAGEIF formula that looks through the Course column (B2:B10) for entries matching the label in A13, and averages the corresponding scores from C2:C10.
- Press Enter.
- Copy the formula from B13 down to B14 and B15.
When you're done, each row in the summary table should show the correct average score for that course.
Need some help?
Hint 1
Watch out: if you leave out the third argument (average_range), Excel will average the Course column instead of the scores and you'll get an error. All three arguments are required for AVERAGEIF to work correctly.
Hint 2
Lock the range and average_range with dollar signs ($B$2:$B$10 and $C$2:$C$10) so those references stay fixed when you copy the formula down. The criteria cell (A13, A14, A15) should stay relative so it shifts with each row.