Logical checks and errors
IFS function: assign ordered score bands without nested IFs
Evaluate checklist scores from highest to lowest threshold because IFS stops at the first TRUE condition. Guard blank cells first and finish with a TRUE catch-all.
Order thresholds deliberately because first TRUE wins
An equipment audit scores machines from 0 to 100: 90 and above earns Pass A, 80–89 earns Pass B, 60–79 requires Review, and below 60 fails. Put machine labels in A2:A5 and scores in B2:B5.
| Machine (A) | Audit score (B) | Expected band |
|---|---|---|
| Press 1 | 82 | Pass B |
| Press 2 | 90 | Pass A |
| Press 3 | 60 | Review |
| Press 4 | Missing score |
Check blank or non-numeric cells first, then step downward
IFS checks condition-result pairs from left to right and returns the result of the very first TRUE test. Placing B2>=60 before B2>=90 would trap a 95 score as Review, so test 90, then 80, then 60, and end with TRUE,"Fail" so lower numbers never fall off the end with #N/A.
Checking NOT(ISNUMBER(B2)) first prevents an empty cell (treated as 0) from silently receiving Fail or a text note (which compares >90) from falsely receiving Pass A.
=IFS(NOT(ISNUMBER(B2)),"Missing score",B2>=90,"Pass A",B2>=80,"Pass B",B2>=60,"Review",TRUE,"Fail")Test every boundary and adjacent score
For B2 = 82, B2>=90 is FALSE and B2>=80 is TRUE, returning Pass B. Test exact cutoffs and one point below each cutoff in the practice grid: 90 -> Pass A, 89 -> Pass B, 80 -> Pass B, 79 -> Review, 60 -> Review, and 59 -> Fail.
Without the final TRUE,"Fail" pair, =IFS(B2>=90,"Pass A",B2>=80,"Pass B",B2>=60,"Review") returns #N/A as soon as B2 drops to 59.
Try the example
Use the original values or change them to test your understanding.