Excel
All Top 50 Excel & Business Modeling Questions#13
Computing Accurate Means with AVERAGEIFS Excluding Zero Values
MediumUberInterview Question #13
Asked at UberYou have sales rep commission data in `A2:B50` where Column A is Department ('SMB', 'Enterprise') and Column B is Commission Paid ($). Some reps received $0 commissions. Write a formula in `D2` to calculate the average commission for the 'Enterprise' department, excluding reps who earned $0.
Input Table: RepCommissions (A1:B5)
4 rows preview| Department (Col A) | Commission (Col B) |
|---|---|
| Enterprise | 5000 |
| Enterprise | 0 |
| SMB | 2000 |
| Enterprise | 7000 |
Expected Output Structure1 rows
| Target Dept | Filtered Mean (D2) |
|---|---|
| Enterprise | 6000 |
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 | Department (Col A) | Commission (Col B) | ||||
| 2 | Enterprise | 5000 | Target [Enter Formula] | |||
| 3 | Enterprise | 0 | ||||
| 4 | SMB | 2000 | ||||
| 5 | Enterprise | 7000 | ||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect