Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to average the last N values in Excel and Google Sheets
Topic:AVERAGE & central tendencyExcel & Google Sheets
=AVERAGE(OFFSET(A1,COUNTA(A1:A7)-3,0,3,1))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1COUNTA(A1:A7) locates the last filled cell in the column.
- 2OFFSET steps to N rows before the end and returns a block N tall.
- 3AVERAGE divides the sum of that block by N — here (40 + 50 + 60) / 3 = 50.
Tips
- Swap both 3s for the window you want — 6 for a six-period trailing average.
- Pair this with a chart to visualise a moving trend.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Is this the same as a moving average?
It is a single trailing average of the last N values. For a moving average that recomputes down a whole column, use AVERAGE over a sliding range in each row.
More on AVERAGE & central tendency
See all →How-to
How to calculate an average
Use the AVERAGE function to calculate the arithmetic mean of a range of numbers in Excel or Google Sheets.
How-toHow to average ignoring zeros
Use AVERAGEIF with a "<>0" criterion to calculate an average that excludes zero values from the calculation.
How-toHow to average ignoring blank cells
AVERAGE naturally ignores blank cells; use AVERAGEIF with "<>" to also ignore text placeholders like dashes.
How-toHow to average with a condition
Use AVERAGEIF to compute the mean of values in one column where a corresponding column meets a specified criterion.
How-toHow to calculate a moving average
Compute a rolling average for each row using AVERAGE with a fixed-window OFFSET or INDEX formula anchored to each row.
How-toHow to find the median of a range
Use the MEDIAN function to find the middle value in a dataset — the value that half the data falls above and half below.