Excel
All Top 50 Excel & Business Modeling Questions#15
Refactoring Deeply Nested IF Statements to Modern IFS and SWITCH
MediumPwCInterview Question #15
Asked at PwCA candidate score in cell `A2` determines a grade: >=90 is 'A', >=80 is 'B', >=70 is 'C', >=60 is 'D', and <60 is 'F'. Write a clean, modern formula in `B2` using IFS without cascading nested parentheses.
Input Table: CandidateScore (A1:A3)
2 rows preview| Score (Col A) | Grade (Col B) |
|---|---|
| 85 | Expected: B |
| 58 | Expected: F |
Expected Output Structure1 rows
| Score (A2) | Assigned Grade (B2) |
|---|---|
| 85 | B |
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 | Score (Col A) | Grade (Col B) | ||||
| 2 | 85 | Expected: B | Target [Enter Formula] | |||
| 3 | 58 | Expected: F | ||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect