Flag missing values with ISBLANK
PremiumIn D2, show "Missing" when the hours in B2 are blank, and otherwise the pay, B2*C2. Use IF with ISBLANK.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Hours | Rate | Pay | ||
| 2 | Jonas | 180 | ||||
| 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 assessment (Indeed format)
Why this works
In a sum a blank cell counts as 0, so =B2*C2 quietly gives 0 pay. ISBLANK(B2) is TRUE for an empty cell, so IF shows "Missing" instead, and the gap gets noticed. Once the hours are typed in, the same formula shows the pay. B2="" works as a test too.
Common mistake: Letting a blank count as 0. A formula that shows 0 looks finished, so missing data goes unnoticed.
The answer
- D2
=IF(1ISBLANK(B2),2"Missing",3B2*C2)
In plain words: 1If the hours in B2 are blank, 2show Missing. 3Otherwise multiply the hours by the rate in C2.