Nested IF with AND in one branch
One band needs two conditions; the next needs only one.
Task:
Year-end awards: Gold needs sales of at least 50,000 and a customer rating of at least 4.5; Silver needs sales of at least 30,000; everyone else gets None. In D2:D7 give each rep's award.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of IF
0/4The same formula in the other shapes it takes at work.
- Nested IF Functions
Create complex conditions using nested IF statements.
CoreAdvanced - Nested IF returning numbers
Bands that return a price rather than a grade.
Intermediate - Nested IF: why the order of tests matters
Test the smallest band first and the bigger ones are never reached.
Advanced - Nested IF with AND in one branch
One band needs two conditions; the next needs only one.
Advanced
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 | |
|---|---|---|---|---|
| 1 | Rep | Sales | Rating | Award |
| 2 | Ava | 62000 | 4.7 | |
| 3 | Ben | 58000 | 4.1 | |
| 4 | Cara | 31000 | 4.9 | |
| 5 | Dan | 29500 | 4.8 | |
| 6 | Eve | 50000 | 4.5 | |
| 7 | Finn | 18000 | 3.9 |
What this exercise teachesMay contain the answer
Each level of a nested IF can carry a different kind of test. Because Gold is checked first, anyone who reaches the Silver test has already failed Gold for one reason or another — Ben on rating — and still lands in the right band.