AVERAGEIFS: an average under two conditions
The typical value for one group, ignoring the small cases.
Task:
You want the typical repair cost for the North region, but only for jobs of at least 100 — the quick fixes distort it. Put that average in B11.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of AVERAGE
0/3The same formula in the other shapes it takes at work.
- AVERAGE with an Outlier
The mean is a fair summary only when nothing in the data is extreme.
CoreBeginner - A trimmed average: drop the highest and lowest
Take out the two extremes before averaging, the way judging panels do.
Intermediate - AVERAGEIFS: an average under two conditions
The typical value for one group, ignoring the small cases.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Job | Region | Cost |
| 2 | R-01 | North | 240 |
| 3 | R-02 | South | 180 |
| 4 | R-03 | North | 45 |
| 5 | R-04 | North | 310 |
| 6 | R-05 | South | 95 |
| 7 | R-06 | North | 160 |
| 8 | R-07 | North | 60 |
| 9 | R-08 | South | 420 |
| 10 | |||
| 11 | North, 100+ |
What this exercise teachesMay contain the answer
A condition can test the same column you are averaging — here it screens out the small jobs before they reach the average. Like SUMIFS and unlike AVERAGEIF, the range to average comes first.