Average order value for one channel and one region — then a threshold on top
AVERAGEIFS averages a range, but only over the rows where every condition you give it holds — the same range/criteria pairs SUMIFS and COUNTIFS use, with a mean at the end instead of a total.
Task:
You handle sales reporting for a home goods retailer that sells through an online store and physical branches. Eight orders came in yesterday, each logged with its channel, region and amount. In D10, work out the average order value for Online orders placed in the East region. In D11, work out the average order value for In-Store orders under $70.
Learning Objectives:
- Filter an AVERAGE by more than one condition with AVERAGEIFS
- Use the same range as both the average range and a criteria range
- Recognize AVERAGEIFS's argument order — the range you're averaging comes before the conditions, same as SUMIFS
Solve without hints for +5 XP
Interactive Spreadsheet
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 | |
|---|---|---|---|---|
| 1 | Order | Channel | Region | Amount |
| 2 | ORD-501 | Online | East | 120 |
| 3 | ORD-502 | Online | West | 85 |
| 4 | ORD-503 | In-Store | East | 60 |
| 5 | ORD-504 | Online | East | 90 |
| 6 | ORD-505 | In-Store | West | 150 |
| 7 | ORD-506 | Online | East | 150 |
| 8 | ORD-507 | In-Store | East | 40 |
| 9 | ORD-508 | Online | West | 65 |
| 10 | Average order value: Online orders in the East region | |||
| 11 | Average order value: In-Store orders under $70 |
What this exercise teaches (contains the answer)
AVERAGEIFS only folds a row's Amount into the mean once every criteria range/criteria pair it was given agrees that row qualifies, which is why D10 skips ORD-502 — an Online order, but in the West, so Region="East" rules it out even though Channel="Online" agrees; only ORD-501, ORD-504 and ORD-506 pass both conditions, averaging to 120. D11 swaps the region condition for a numeric one applied to the very column being averaged: "<70" is a piece of text just like "Online" is, but AVERAGEIFS reads it as a comparison rather than an exact match, so of the three In-Store orders only ORD-503 and ORD-507 clear the threshold — ORD-505's $150 order matches the channel but fails it and drops out, leaving an average of 50. Both formulas are the same skeleton — average this range, but only where these conditions hold — with different conditions plugged in.