Array FunctionsAdvanced
Capstone: build a live regional summary
Everything in this track, in one report.
Task:
Finance wants a summary that maintains itself. Three cells, three formulas. In E2, list each region once from B2:B9. In F2, list the same regions ordered by name. In G2, list the order numbers from A2:A9 for orders over 1000.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Regions | Sorted | Large orders | |
| 2 | SO-1041 | North | 1200 | ||||
| 3 | SO-1042 | South | 890 | ||||
| 4 | SO-1043 | North | 2100 | ||||
| 5 | SO-1044 | East | 640 | ||||
| 6 | SO-1045 | South | 1750 | ||||
| 7 | SO-1046 | North | 430 | ||||
| 8 | SO-1047 | West | 980 | ||||
| 9 | SO-1048 | East | 1310 |
What this exercise teachesMay contain the answer
Three formulas and the report maintains itself. Add an order next month and every one of them resizes. F2 pointing at E2# rather than E2:E5 is the detail that makes it durable — hardcode the range and it breaks the first time a new region appears.