IFS checks its conditions in order and stops at the first one that is TRUE, so a tier ladder only works when the strictest test comes first, and a TRUE at the end is what catches everyone who clears none of the bars.
You run supplier performance reviews for a food manufacturer's purchasing team. Each supplier is graded on its on-time delivery rate for the quarter: 95% or better is "Preferred", 88% or better is "Approved", 75% or better is "Probation", and anything below 75% is "Suspend". In column C, use a single IFS formula to give each supplier its tier from the on-time percentage in column B. Fill in rows 2 through 7.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 | |
|---|---|---|---|
| 1 | Supplier | On-Time Delivery (%) | Tier |
| 2 | Brightline Packaging | 99.1 | |
| 3 | Kessler Fasteners | 95 | |
| 4 | Moreno Plastics | 94.9 | |
| 5 | Halvorsen Steel | 88 | |
| 6 | Ashby Labels | 75 | |
| 7 | Tidewater Pallets | 62 |
IFS reads its tests left to right and hands back the result paired with the first one that is TRUE, which is why the order of the bars matters as much as the bars themselves. Brightline's 99.1 is at least 95, at least 88 and at least 75, so three tests are true for it; putting B2>=95 first is what makes "Preferred" the one that wins. Write the ladder bottom-up, with B2>=75 first, and every supplier from Brightline to Ashby stops at that first test and comes back "Probation", a formula that looks reasonable and silently demotes the best supplier on the list. The boundary rows check the comparisons: Kessler at exactly 95 and Halvorsen at exactly 88 land in the higher tier only because the rule says "or better" and the tests use >=, while Moreno at 94.9 misses "Preferred" by a tenth of a point and drops to "Approved". Tidewater at 62 fails all three tests, and without a final pair IFS would have nothing to return and show #N/A. TRUE as the last test always passes, so it acts as the "everyone else" branch and turns that error into "Suspend".