About this lesson
IFNA, ISNUMBER, ISBLANK and IFERROR: handling errors in a comps table in Google Sheets
In Google Sheets, IFNA replaces only #N/A: =IFNA(B2/C2,"n/a"). IFERROR replaces every error, including a #DIV/0! from a missing input that should be fixed, so use it only when you know which error you're expecting. ISNUMBER and ISBLANK return TRUE or FALSE, which makes them good data checks inside IF.
Using Excel? Do this lesson in Excel.
=IFNA(B2/C2,"n/a")What you'll practise
Show n/a for peers with no broker estimate, flag missing data with ISNUMBER and ISBLANK, and see why IFERROR would have hidden a real problem.
4 steps · 40 points · the cells outlined in gold below
- IFNA
- IFERROR
- ISNUMBER
- ISBLANK
- data checks
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Company | Price ($) | EPS FY27 ($) | P/E (x) | EPS check |
| 2 | Wombat Energy | 4.82 | 0.41 | ||
| 3 | Quokka Software | 12.65 | #N/A | ||
| 4 | Coral Coast Resorts | 2.37 | 0.19 | ||
| 5 | Mallee Grain | 6.14 | 0.52 | ||
| 6 | Tasman Ports | 3.94 | |||
| 7 | Kimberley Cement | 1.88 | 0.13 | ||
| 8 | Otway Foods | 8.46 | 0.61 | ||
| 9 | |||||
| 10 | Average P/E | #DIV/0! |