Dynamic Arrays
Average or sum the last N numeric entries with FILTER and TAKE
Select the last N numeric cells in source-row order, retaining zero and negative values while skipping text, blanks and error cells.
Skip blank rows and "Maintenance" text notes when averaging the last N shifts!
Filter numeric values before taking the last N rows. Otherwise blanks or text at the end consume positions without contributing numeric observations. Numeric zero and negative numbers remain eligible; numeric text, logical values and error cells are excluded by this exercise's policy. Skipping errors is a deliberate policy, not evidence the underlying data is correct.
Last means bottommost in source-row order. Sort a log chronologically first if that order should represent recency; this formula does not inspect timestamps. Dates stored as numeric serials also qualify as numbers, so keep the range restricted to the intended measurements.
N must be a positive integer. If fewer than N numeric entries exist, use all available numbers. If none exist, return No numeric data rather than treating an empty dataset as a genuine zero total.
| Shift Row | Output (Col B) | ISNUMBER(B) | Included in Last 3 Numeric? |
|---|---|---|---|
| Shifts 1–2 | 110, 130 | TRUE, TRUE | Older valid runs (dropped by TAKE -3) |
| Shift 3 | "Maint" | FALSE | Skipped cleanly! |
| Shift 4 | 140 | TRUE | Included (#1 of last 3) |
| Shift 5 | (blank) | FALSE | Skipped cleanly! |
| Shifts 6–7 | 160, 180 | TRUE, TRUE | Included (#2 and #3 -> Avg = 160!) |
Rolling Average of Last N Valid Numeric Values
Retains only numeric entries via FILTER(B2:B8, ISNUMBER(B2:B8)), takes the bottom D2 values, and averages them.
=IF(NOT(ISNUMBER(D2)),"Invalid N",IF(OR(D2<1,D2<>INT(D2)),"Invalid N",IF(COUNT(B2:B8)=0,"No numeric data",AVERAGE(TAKE(FILTER(B2:B8, ISNUMBER(B2:B8)), -D2)))))Sum of Last N Valid Numeric Values
Replaces AVERAGE with SUM to total the most recent N active shifts.
=IF(NOT(ISNUMBER(D2)),"Invalid N",IF(OR(D2<1,D2<>INT(D2)),"Invalid N",IF(COUNT(B2:B8)=0,"No numeric data",SUM(TAKE(FILTER(B2:B8, ISNUMBER(B2:B8)), -D2)))))Try the example
Use the original values or change them to test your understanding.