Three outcomes with nested IF
PremiumGrade the score in B2: 90 or more is "A", 70 or more is "B", anything lower is "C". In C2, put a second IF inside the first.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Grade | |||
| 2 | Jonas | 76 | ||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock IF and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for job interviews
Why this works
Excel reads a nested IF from the outside in and stops at the first TRUE test, so test the highest band first. 76 is not >= 90, so the second IF runs: 76 >= 70 is TRUE, giving B.
Common mistake: Testing the lowest band first. Excel stops at the first true test, so start with the highest grade.
The answer
- C2
=IF(1B2>=90,2"A",3IF(B2>=70,"B","C"))
In plain words: 1If the score in B2 is 90 or more, 2give an A. 3Otherwise run a second IF: B from 70, C below that.