AdvancedData Analysis
Name the second-best rep with LARGE and INDEX
Combine ranking with a lookup to return a name, not just a number.
LARGE · INDEX · MATCH
14 min40 points
Build a regional summary from raw orders
The situation
Harbor Supply's board pack needs a one-line summary per region: how many orders, what they totalled, and the average size. You have the raw order export.
A1
fx
| Row number | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Value | Region | East summary | ||||
| 2 | ORD-1041 | $1,250 | West | Orders | ||||
| 3 | ORD-1042 | $3,400 | East | Total | ||||
| 4 | ORD-1043 | $890 | West | Average | ||||
| 5 | ORD-1044 | $2,100 | East | |||||
| 6 | ORD-1045 | $1,675 | East | |||||
| 7 | ORD-1046 | $4,250 | East | |||||
| 8 | ORD-1047 | $2,980 | North | |||||
| 9 | ORD-1048 | $1,455 | East | |||||
| 10 |
Functions in this exercise
Who this is for
Recommended next
Related exercises
- IntermediateMath Functions
Total sales for a region and month with SUMIFS
Apply two conditions at once to a conditional sum.
SUMIFS
9 min25 points - AdvancedData Analysis
Name the second-best rep with LARGE and INDEX
Combine ranking with a lookup to return a name, not just a number.
LARGE · INDEX · MATCH
14 min40 points - BeginnerStatistical Functions
Count orders above a threshold with COUNTIF
Count how many rows meet a numeric condition.
COUNTIF
5 min15 points