Excel
All Top 50 Excel & Business Modeling Questions#8
Tiered Commission & Tax Calculation with Approximate Match
MediumMorgan StanleyInterview Question #8
Asked at Morgan StanleyA sales compensation table is structured in `A2:B6` where Column A contains Revenue Thresholds (`$0, $10,000, $25,000, $50,000, $100,000`) and Column B contains Commission Rates (`2%, 5%, 8%, 12%, 15%`). In cell `D2`, a sales rep generates `$34,500`. Write a formula in `E2` to lookup the eligible commission rate using approximate matching.
Input Table: TierBrackets (A1:B6)
5 rows preview| Min Revenue (Col A) | Commission Rate (Col B) |
|---|---|
| 0 | 0.02 |
| 10000 | 0.05 |
| 25000 | 0.08 |
| 50000 | 0.12 |
| 100000 | 0.15 |
Expected Output Structure1 rows
| Sales Revenue (D2) | Assigned Commission (E2) |
|---|---|
| 34500 | 0.08 |
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 | Min Revenue (Col A) | Commission Rate (Col B) | ||||
| 2 | 0 | 0.02 | Target [Enter Formula] | |||
| 3 | 10000 | 0.05 | ||||
| 4 | 25000 | 0.08 | ||||
| 5 | 50000 | 0.12 | ||||
| 6 | 100000 | 0.15 | ||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect