Excel
All Top 50 Excel & Business Modeling Questions#10
Dynamic Rolling Ranges Using the OFFSET Function
MediumDeloitteInterview Question #10
Asked at DeloitteMonthly revenues are entered chronologically down Column B starting at `B2`. Cell `B1` contains the total count of completed months (`=COUNT(B2:B100)`). Write a formula in `D2` using OFFSET to calculate the sum of the last 3 completed months.
Input Table: MonthlyRevenue (B1:B7)
6 rows preview| Row / Cell | Value |
|---|---|
| B1 (Count of Months) | 5 |
| B2 (Jan) | 10000 |
| B3 (Feb) | 12000 |
| B4 (Mar) | 15000 |
| B5 (Apr) | 18000 |
| B6 (May) | 22000 |
Expected Output Structure1 rows
| Metric | Formula Output (D2) |
|---|---|
| Trailing 3-Month Sum (Mar+Apr+May) | 55000 |
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 | Row / Cell | Value | ||||
| 2 | B1 (Count of Months) | 5 | Target [Enter Formula] | |||
| 3 | B2 (Jan) | 10000 | ||||
| 4 | B3 (Feb) | 12000 | ||||
| 5 | B4 (Mar) | 15000 | ||||
| 6 | B5 (Apr) | 18000 | ||||
| 7 | B6 (May) | 22000 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect