AVERAGEIF: the average of one group
The average order, but only for the channel you were asked about.
Task:
Marketing wants to know whether online customers spend more per order than shop customers. Put the average online order in E2 and the average in-store order in E3.
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 Function
The number you quote when someone asks what a typical month costs.
CoreBeginner - AVERAGE: a blank is not a zero
The same function gives two different answers depending on what an empty month means.
Intermediate - AVERAGEIF: the average of one group
The average order, but only for the channel you were asked about.
Intermediate
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 | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Channel | Value | Channel | Average order | |
| 2 | 1041 | Online | 64 | Online | ||
| 3 | 1042 | In store | 38 | In store | ||
| 4 | 1043 | Online | 92 | |||
| 5 | 1044 | In store | 45 | |||
| 6 | 1045 | Online | 71 | |||
| 7 | 1046 | In store | 29 | |||
| 8 | 1047 | Online | 58 |
What this exercise teachesMay contain the answer
AVERAGEIF is AVERAGE with a filter in front of it. Pointing the criterion at E2 rather than typing "Online" into the formula means the same formula serves every channel in the table, and a new channel only needs a new row.