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.
Next with premium:COUNTIFSUMIF
Need help?
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.