IntermediateLogical Functions
Band orders by size with nested IF
Return one of three labels depending on where a value falls.
IF
9 min25 points
Flag salaries outside their band
The situation
Harbor Supply is auditing pay before the annual review. Every grade has a minimum and a maximum, and HR needs to know which salaries have drifted outside their band in either direction — both are a problem, for different reasons.
A1
fx
| Row number | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Employee | Salary | Band min | Band max | Check | |
| 2 | Dana Whitfield | $71,000 | $52,000 | $68,000 | ||
| 3 | Marcus Bell | $58,000 | $52,000 | $68,000 | ||
| 4 | Priya Raman | $47,000 | $52,000 | $68,000 | ||
| 5 | Tom Alvarez | $61,500 | $52,000 | $68,000 | ||
| 6 | Sara Kim | $68,000 | $52,000 | $68,000 | ||
| 7 |
Functions in this exercise
- OR — Returns TRUE when at least one condition is true. Syntax and example
- IF — Returns one value when a test is true and another when it is false. Syntax and example
Who this is for
Recommended next
Related exercises
- BeginnerLogical Functions
Flag orders needing special handling with OR
Mark an order when either of two independent conditions is true.
OR · IF
5 min15 points - IntermediateLogical Functions
Flag accounts for review with AND and OR
Combine two logical tests so one condition can override the other.
AND · OR · IF
9 min30 points - BeginnerLogical Functions
Flag reps below target with IF
Use IF to label each rep as meeting or missing their target.
IF
5 min15 points