Compare two cells with IF
PremiumIn D2, show "Over budget" if the amount spent in C2 is more than the budget in B2, and "OK" if not.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Department | Budget | Spent | Status | ||
| 2 | Marketing | 50000 | 54200 | |||
| 3 | Sales | 80000 | 76500 | |||
| 4 | IT | 120000 | 118300 | |||
| 5 | HR | 30000 | 31900 | |||
| 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 assessment (Indeed format)
Why this works
The test in IF can compare two cells, not just a cell and a fixed number. Marketing spent 54,200 against a budget of 50,000, so C2>B2 is TRUE and IF returns "Over budget". Filled down, Sales and IT show OK and HR is over.
Common mistake: Turning the comparison around, B2>C2, which flags the departments that are under budget.
The answer
- D2
=IF(1C2>B2,2"Over budget",3"OK")
In plain words: 1If the amount spent in C2 is more than the budget in B2, 2show Over budget. 3Otherwise show OK.