Math & Trig
Sum or average the last N entries in a column (skipping blank rows)
Extract the final N numeric observations from an expanding log with TAKE and FILTER—keeping legitimate zeros while skipping trailing blanks.
Why TAKE(-N) needs FILTER(ISNUMBER) on live tracking logs
TAKE(A2:A6, -3) grabs the bottom 3 cells of a range, which accidentally pulls empty pre-formatted rows at the bottom of a template.
Filtering for real numbers first—FILTER(A2:A6, ISNUMBER(A2:A6))—strips blanks and text notes while keeping genuine 0 sales days intact. Passing -N to TAKE then slices the most recent N recorded numbers.
| Day (A) | Output (B) | Included in Last 3? |
|---|---|---|
| Mon | 120 | No (older) |
| Tue | 200 | Yes (#1 of last 3) |
| Wed | 0 | Yes (#2 — valid zero kept!) |
| Thu | (blank) | Skipped (blank) |
| Fri | 280 | Yes (#3 of last 3) |
Sum and average last N numeric values
Last 3 recorded numbers are 200, 0, and 280 -> Sum = 480 and Average = 160.
=IF(OR(C2<1, C2<>INT(C2)), "Invalid count", IF(COUNT(B2:B6)=0, "No numeric data", SUM(TAKE(FILTER(B2:B6, ISNUMBER(B2:B6)), -C2))))Trailing N-entry average
Swap SUM for AVERAGE to compute a rolling trailing-N mean without volatile OFFSET.
=IF(OR(C2<1, C2<>INT(C2)), "Invalid count", IF(COUNT(B2:B6)=0, "No numeric data", ROUND(AVERAGE(TAKE(FILTER(B2:B6, ISNUMBER(B2:B6)), -C2)), 2)))Inputs and interpretation
N must be a positive integer. If fewer than N numbers exist, TAKE returns all available numeric observations. Last means physical row order, not most recent date: keep the log sorted deliberately. Numeric zero is included; numeric-looking text is excluded.
Try the example
Use the original values or change them to test your understanding.