BeginnerStatistical Functions
Average one region's orders with AVERAGEIF
Average only the rows that match a condition, in one function.
AVERAGEIF
5 min15 points
AVERAGEIF
Averages only the cells that meet one condition.
Syntax
AVERAGEIF(range, criteria, [average_range])
Statistical Functions.
Practise AVERAGEIF1 interactive exercise, free, no account needed
A worked example
=AVERAGEIF(B2:B9,"West",C2:C9)
Returns 1,800
Averages the values in C for the rows whose region in B is West, and ignores the rest.
Where it goes wrong
Leaving off the third argument averages the criteria column itself. On a text column there is nothing to average, so the result is #DIV/0! rather than a wrong number.
What the exercises catch
Each of these is a wrong turn the practice exercises recognise and name. They are the misconceptions, not the answers.
- That averages every order in the list, which is the company-wide figure the sales lead already has. The average has to look at the region column and keep only the West rows.Average one region's orders with AVERAGEIF
- Without a third argument, AVERAGEIF averages the column it searched — and a column of region names has nothing to average, so you get #DIV/0!. The values to average are in C2:C9.Average one region's orders with AVERAGEIF
Practise AVERAGEIF
Knowing what a function does and being able to write it from memory are different skills. These close the gap.
All AVERAGEIF practice exercises →