Three-plus bands: IFS, nested IF, and cut()

More than two outcomes

A two-way IF handles Large / Small. Three or more bands — S under $300, M from $300 to under $1,000, L at $1,000 and up — need a chain of tests, and the order of that chain is the whole lesson.

Worked example — IFS, strictest first

IFS (Sheets and Excel 2019+) takes pairs of condition, value and returns the value of the first condition that is TRUE:

=IFS(D2>=1000, "L", D2>=300, "M", TRUE, "S")

Read it top down for order 1005 ($1,320): is it ≥ 1000? Yes — L, stop. For order 1001 ($420): ≥ 1000? No. ≥ 300? Yes — M. Order 1002 ($180) fails both and falls through to the last pair, TRUE, "S": a condition that is always true, the catch-all that gives every remaining row its band and stops IFS returning #N/A when nothing matched. Filled down: S 4 · M 7 · L 1.

Now put the tests in the other order — IFS(D2>=300, "M", D2>=1000, "L", TRUE, "S") — and order 1005 is tested against >=300 first, passes, and is labelled M. The L test is never reached, and the count comes out S 4 · M 8 · L 0. With overlapping >= conditions the strictest test goes first; the same holds for a nested IF:

=IF(D2>=1000, "L", IF(D2>=300, "M", "S"))

The inner IF sits in the outer one's false slot, so it only sees rows that failed >=1000. Two levels are readable; four are not, and that is when IFS — or a lookup table (Lesson 6.4) — takes over.

The same bands in R: cut()

R bands a numeric vector with cut, which takes the breaks (the edges) and one label per interval:

> orders$band <- cut(orders$amount, breaks = c(0, 300, 1000, Inf),
+                    labels = c("S", "M", "L"), right = FALSE)
> table(orders$band)

S M L 
4 7 1 

Four breaks — 0, 300, 1000, Inf — make three intervals, so there are three labels; hand cut four labels and it stops with number of intervals and length of 'labels' differ. Inf is the open top. right = FALSE makes each interval closed on the left, [300, 1000), so that an amount of exactly 300 is M — the same reading as >=300 in the IFS. Without it, cut's default is right-closed, (300, 1000], and 300 would be S. The default also has a sharp edge: the lowest break is excluded, so a value of exactly 0 becomes NA unless include.lowest = TRUE is added. No order here is $0 or sits on a boundary, so the counts match under either convention — but the quiz asks about the rule, and the rule is: default (lo, hi], right = FALSE for [lo, hi).

levels(cut(orders$amount, breaks = c(0, 300, 1000, Inf))) shows the labels R invents when none are given — "(0,300]" "(300,1e+03]" "(1e+03,Inf]" — which is why labels is worth supplying.

Type three, then count the bands

Type the IFS, the nested IF and the cut. The worksheet asks for the three band counts; the code block reproduces them with an if / elif / else chain — Python's nested IF, again strictest first.

CRISP-DM: this is data preparation → construct data.