Logical FunctionsIntermediate

Grade Suppliers into Scorecard Tiers with IFS

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.

Task:

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.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

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.

7 rows × 3 columns6 cells you fill in
ABC
1SupplierOn-Time Delivery (%)Tier
2Brightline Packaging99.1
3Kessler Fasteners95
4Moreno Plastics94.9
5Halvorsen Steel88
6Ashby Labels75
7Tidewater Pallets62
What this exercise teachesMay contain the answer

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".