Sort machined shafts into Pass, Rework, or Scrap from two tolerance windows
IF can nest inside its own false branch to choose between three outcomes instead of two, and AND is what turns each one into a real range check rather than a single-sided comparison.
Task:
You run quality control on the shop floor at a machine shop that turns shafts for hydraulic pumps. Each shaft has a narrow spec window it needs to land inside to pass, and a wider scrap window around that — anything outside spec but still inside the scrap window gets sent to rework instead of straight to the bin. Classify each shaft as Pass, Rework, or Scrap in column G.
Learning Objectives:
- Nest a second IF inside the first one's false branch to choose between three outcomes, not two
- Use AND to test that a value sits inside both ends of a range in a single condition
- Order range checks from narrowest to widest so each ELSE branch only has to cover what the previous test ruled out
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 | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Part ID | Measured (mm) | Spec Min | Spec Max | Scrap Min | Scrap Max | Result |
| 2 | SH-101 | 25.02 | 24.95 | 25.05 | 24.85 | 25.15 | |
| 3 | SH-102 | 25.09 | 24.95 | 25.05 | 24.85 | 25.15 | |
| 4 | SH-103 | 24.88 | 24.95 | 25.05 | 24.85 | 25.15 | |
| 5 | SH-104 | 25.2 | 24.95 | 25.05 | 24.85 | 25.15 | |
| 6 | SH-105 | 24.95 | 24.95 | 25.05 | 24.85 | 25.15 |
What this exercise teaches (contains the answer)
IF(AND(B2>=C2,B2<=D2),"Pass",...) tests the spec window first: 25.02 clears 24.95 and 25.05 on both sides, so SH-101 passes before the formula ever looks at the scrap window. When that first AND comes back false — 25.09 beats the spec max of 25.05 — the formula falls into its second branch, which runs the same two-sided test against the wider 24.85-to-25.15 scrap window; both 25.09 and 24.88 clear that one, so SH-102 and SH-103 land on Rework. SH-104 at 25.20 fails both tests, landing past the scrap max too, so "Scrap" is what's left once neither AND has fired. SH-105 at exactly 24.95 shows why >= and <= matter here: a measurement sitting right on the boundary is still a Pass, not a near-miss flagged for rework.