Dynamic Arrays
Sum and average the last N columns horizontally (rolling trailing months with TAKE)
Calculate trailing 3-month or trailing N-period sums and averages across horizontal monthly P&L columns using TAKE(row, , -N).
Take the rightmost N numeric observations
A negative third argument to TAKE selects the rightmost columns. FILTER with ISNUMBER first removes nonnumeric cells while retaining genuine zero values.
This returns the latest N numeric observations in the supplied range, which are not necessarily N consecutive calendar months when there are gaps. Keep columns ordered chronologically and extend the fixed range when adding periods.
Use a positive whole-number N no greater than the number of numeric observations. An annualized average is a scenario based on that recent rate, not a forecast that accounts for seasonality. Seeded values are in thousands of currency units.
| Jan (B) | Feb (C) | Mar (D) | Apr (E) | May (F) | Jun (G) | Trailing 3-Mo KPI |
|---|---|---|---|---|---|---|
| $40k | $45k | $50k | $55k | $65k | $80k | Last 3 Sum = $200k, Avg = $66.67k |
| $40k | $45k | $50k | $55k | (blank) | (blank) | Populated Last 3 (Feb–Apr) = $150k! |
Sum and average the last N populated horizontal columns
Leaving TAKE's 2nd [rows] argument empty (two commas: , , -H2) keeps all rows while slicing the rightmost H2 columns.
=IF(OR(NOT(ISNUMBER(H2)),H2<1,MOD(H2,1)<>0,H2>COUNT(B2:G2)),"Invalid window",SUM(TAKE(FILTER(B2:G2, ISNUMBER(B2:G2)), , -H2)))Trailing N-column average (for annualized run-rate projections)
Multiply this 3-month trailing average by 12 to compute a smoothed Annualized Run Rate.
=IF(OR(NOT(ISNUMBER(H2)),H2<1,MOD(H2,1)<>0,H2>COUNT(B2:G2)),"Invalid window",ROUND(AVERAGE(TAKE(FILTER(B2:G2, ISNUMBER(B2:G2)), , -H2)), 2))Try the example
Use the original values or change them to test your understanding.