How to average the last N values in a growing column
✓ Verified in LibreOffice 25.8.7.3A rolling average of the most recent entries — last 3 months, last 7 days — that stays current as rows are added.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =AVERAGE(OFFSET(A2,COUNT(A2:A1000)-3,0,3,1)) | Change both 3s for a different window. Excel 365: =AVERAGE(TAKE(FILTER(A2:A1000,A2:A1000<>""),-3)). |
| Google Sheets | =AVERAGE(OFFSET(A2,COUNT(A2:A1000)-3,0,3,1)) | Identical. |
| LibreOffice Calc | =AVERAGE(OFFSET(A2,COUNT(A2:A1000)-3,0,3,1)) | Identical; the TAKE alternative needs 25.8+. |
How it works
COUNT finds how many numbers exist (5), so OFFSET starts 5−3=2 rows below the top and grabs a 3-row window — the last three values (30, 40, 50), averaging to 40. As new rows arrive the window slides automatically. OFFSET is volatile (recalculates on every change), which is fine at this scale; the TAKE version in the notes is the calmer modern equivalent. Works with SUM or MAX in place of AVERAGE for rolling totals and peaks.
Verified, not just documented
We ran =AVERAGE(OFFSET(A2,COUNT(A2:A6)-3,0,3,1)) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 40 — exactly the expected result. Every formula here is confirmed by actually executing it.