Average the Last N Values

Excel Formulas › Average

All versionsAVERAGE

Score the most recent N entries no matter how the list grows — last 3 sales, last 5 readings. OFFSET anchors a window at the bottom of the data, and AVERAGE reads it.

Quick formula: average the last N rows of B:B:
=AVERAGE(OFFSET(B1, COUNT(B:B)-N+1, 0, N, 1))
COUNT finds the last filled row; OFFSET carves out the final N cells for AVERAGE.

Functions used (tap for the full reference guide):

AVERAGEOFFSET

The example

The window slides to the newest values.

AB
1ReadingAvg last 3
28—
36
4108

The formula

The formula:

=AVERAGE(OFFSET(B1, COUNT(B:B)-N+1, 0, N, 1)) // last N, auto-updating

How it works

How it works:

  1. COUNT(B:B) counts the filled numeric cells — the position of the last value.
  2. OFFSET(B1, COUNT-N+1, 0, N, 1) builds a height-N window ending at that last value.
  3. AVERAGE then means just those final N cells.
  4. Add a new row and the window slides automatically — no formula edit needed.

OFFSET is volatile (recalculates often). For a non-volatile version on Excel 365, use =AVERAGE(TAKE(B2:B100, -N)) — TAKE with a negative count grabs the last N rows directly.

Try it: interactive demo

Live demo

Values and N.

Average of last N:

Variations

365 version

Non-volatile TAKE:

=AVERAGE(TAKE(B2:B100, -N))

Sum the last N

Total instead:

=SUM(OFFSET(B1, COUNT(B:B)-N+1, 0, N, 1))

Last single value

Most recent:

=LOOKUP(2, 1/(B:B<>""), B:B)

Pitfalls & errors

COUNT vs COUNTA. COUNT only sees numbers; use COUNTA if the column holds text or mixed data.

Gaps break it. OFFSET assumes a contiguous block — blanks inside the data shift the window.

OFFSET is volatile. It recalculates on every change; prefer TAKE on 365.

Practice workbook

📊
Download the free Average the Last N Values practice workbook
A last-N-average sheet with the TAKE, sum-last-N, and last-value variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I average the last N values in Excel?
Use =AVERAGE(OFFSET(B1, COUNT(B:B)-N+1, 0, N, 1)). COUNT finds the last row and OFFSET builds a height-N window that slides as data grows.
Is there a simpler way in Excel 365?
Yes: =AVERAGE(TAKE(B2:B100, -N)) grabs the last N rows without the volatile OFFSET.
Why does my window include wrong cells?
Blank cells inside the data or using COUNT on text columns shift the count. Keep the data contiguous and match COUNT/COUNTA to the data type.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Moving average · OFFSET dynamic range · Take & drop

Function references: AVERAGE · OFFSET