Chapter 08 · 8 min · Basic
Putting rows into buckets
How many of our orders are small? There is no column called small. You have amounts, and somebody wants them sorted into groups — which means you are about to invent the groups, and every number that follows is a consequence of where you put the lines.
That is not a reason to avoid it. Banding is how a list of numbers becomes something a person can hold in their head. It is a reason to do it out loud: name the bands, say who owns each boundary, and check the bands actually add up to the rows you started with.
This chapter is mostly about that last check, because it is the one everybody skips.
The grid
Ten orders in A1:B11 — order_id and amount_myr. Nothing in the sheet says which band an order belongs to; that is the part you are adding. Work in column D.
Naming the bands
Start by writing the bands down as a rule, in words, before any formula: small is under 50, medium is 50 up to under 100, large is 100 and above. Three bands, no overlaps, no gaps.
IFS walks the conditions in order and stops at the first true one, which is what makes the rule readable — it reads almost exactly like the sentence above.
Row 5 is 120.00. Run it.
Row 5 is 120.00 and the rule says large starts at 100. Which word comes back?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | order_id | amount_myr | ||
| 2 | 5001 | 18.00 | =IFS(B5<50,"small",B5<100,"medium",B5>=100,"large") | |
| 3 | 5002 | 50.00 | ||
| 4 | 5003 | 96.40 | ||
| 5 | 5004 | 120.00 | ||
| 6 | 5005 | 49.99 | ||
| 7 | 5006 | 50.00 | ||
| 8 | 5007 | 231.50 | ||
| 9 | 5008 | 7.25 | ||
| 10 | 5009 | 100.00 | ||
| 11 | 5010 | 64.80 |
Basic
The rest of this chapter is Basic
The Excel tutorial and Spreadsheet Fundamentals are free here, forever. This track is the paid half: what to actually do with a spreadsheet once somebody sends you one, from the first look to a summary you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Somebody owns the boundary
- 03The gap you cannot see
- 04Bands that add up
- 05Moving a line moves every number
- 06A band rule worth handing over