Excel
All Top 50 Excel & Business Modeling Questions#16
Trapping Calculation Errors Gracefully with IFERROR and IFNA
EasyDeloitteInterview Question #16
Asked at DeloitteYou have Prior Year Sales in Col A and Current Year Sales in Col B. In Col C, write a formula to calculate Year-over-Year Growth `(B2 - A2) / A2`. If Prior Year Sales is zero or blank, display 0 instead of `#DIV/0!`. Explain whether to use IFERROR or IFNA.
Input Table: YoYData (A1:B4)
3 rows preview| Prior Year (Col A) | Current Year (Col B) |
|---|---|
| 100 | 150 |
| 0 | 50 |
| 80 | 80 |
Expected Output Structure3 rows
| Prior Year (A) | Current Year (B) | YoY Growth (C) |
|---|---|---|
| 100 | 150 | 0.5 |
| 0 | 50 | 0 |
| 80 | 80 | 0 |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Prior Year (Col A) | Current Year (Col B) | ||||
| 2 | 100 | 150 | Target [Enter Formula] | |||
| 3 | 0 | 50 | ||||
| 4 | 80 | 80 | ||||
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect