Calculating the Average of Five Values Near the End of a Range

Written by Allen Wyatt (last updated July 12, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365


2

Alex has a column of values for which he needs an average of the last five values, but one. For instance, if there is data in cells A5:A36, he needs the average of the values in A31:A35, ignoring cell A36 (the "but one"). The formula needs to be dynamic, as data will be routinely added to the column.

Excel is nothing if not flexible, so there are multiple ways that this average could be computed. The first relies on the use of the OFFSET function and (obviously) the AVERAGE function:

=AVERAGE(OFFSET(A5,COUNTA(A:A)-6,0,5))

The formula works correctly, provided there are no empty rows or rows including non-numeric values. Thus, Alex should use this formula only if he has numeric values in A5 through some cell below A5. Further, it will not work correctly if the cells in A1:A4 contain other information, such as text headings.

A better solution may be to simply look at column A in its entirety, as is done in this formula:

=AVERAGE(OFFSET(A1,LOOKUP(2,1/(A:A<>""),ROW(A:A))-6,0,5,1))

This one is pretty cool, but it takes a bit of explaining. The key part of the formula is getting the row number for the last row with data. This is done using this portion of the formula:

LOOKUP(2,1/(A:A<>""),ROW(A:A))

The A:A<>"" portion creates an array that returns TRUE for non-blank cells and FALSE for blank cells. Expanding this to 1/(A:A<>"") converts the array of TRUE and FALSE values to an array with 1s (the TRUE, non-blank cells) or #DIV/0! errors (the FALSE, blank cells).

The LOOKUP function will then look for the value 2 in that array. Since there are no 2s in the range, it returns the row number from ROW(A:A) for the last occurrence of 1. In other words, it returns the row number of the row containing the last non-blank cell. Subtracting 6 from the row number yields that row where you want to start your average, and this is all plugged into the AVERAGE function to get the average of the five rows beginning at that point.

If you are using Excel 365, then I find this formula quite helpful:

=AVERAGE(TAKE(DROP(TRIMRANGE(A:A,2),-1),-5))

The TRIMRANGE function "trims" the bottom empty rows off of the range of A:A. Thus, you end up with a range from A1 through the last cell in column A that contains a value. This is fed into the DROP function, which drops a single row (-1) from the bottom of the range. This adjusted range is then fed into the TAKE function, which returns a subset of cells consisting of the last 5 cells from the bottom—the exact rows that Alex wanted. These 5 are then averaged.

As of this writing, this formula will only work properly in the version of Excel in Microsoft 365 because that's the only version that includes the TRIMRANGE function. Both DROP and TAKE are also available in Excel 2024.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13256) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using an Exact Number of Digits

Excel allows you to format numeric data in all sorts of ways, but specifying a number of digits independent of the ...

Discover More

Complex Searches for Documents

When working with lots of documents, you may have need from time to time to discover which of those documents contain ...

Discover More

Updating a Field in a Text Box

If you put a field into a text box, you might be surprised to find that it doesn't update when you try to update all your ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Returning Zero When a Referenced Cell is Blank

Reference a cell in a macro, and if that cell is blank Excel normally equates that to a zero value. What if you don't ...

Discover More

Getting a Conditional Count of Cells Containing Values

Excel provides several worksheet functions that can be used to count cells containing values--"particularly numeric ...

Discover More

Formula Shows Instead of Formula Result

When you enter a formula in a cell, you expect Excel to display the result of that formula once you are completed. If ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 6?

2025-07-13 15:35:33

Alec W

Thanks to everyone concerned for the assistance. Also to Puneet Gogia for giving me a lead. What I ended up with is this:

=IFERROR(AVERAGE(TAKE(DROP(TAKE(FILTER(Table1[Player1], Table1[Player1]<>""), -6), -1), -5)),"-")

Works like a charm, and I learned a new trick!


2025-07-12 11:44:25

J. Woolley

The Tip says, "...there are multiple ways...." Here are some more.
The Tip's last formula requires Excel 365, so you might as well use the new dot (.) operator to trim trailing blanks instead of TRIMRANGE:
    =AVERAGE(TAKE(DROP(A:.A, -1), -5))
The Tip's first two formulas need the last non-blank row for use in OFFSET. With the dot operator, the last non-blank row is MAX(ROW(A:.A)), so this will work:
    =AVERAGE(OFFSET(A1, MAX(ROW(A:.A)) - 6, 0, 5))
Without Excel 365, MAX((A:A <> "") * ROW(A:A)) gives the last non-blank row, so this will also work:
    =AVERAGE(OFFSET(A1, MAX((A:A <> "") * ROW(A:A)) - 6, 0, 5))
Finally, here's a user-defined function (UDF):

Function AvgLast5But1(Target As Range)
    Dim nLast As Long, nCells As Long, rLast5But1 As Range
    If Target.Columns.Count <> 1 Or Target.Rows.Count < 6 Then
        AvgLast5But1 = CVErr(xlErrValue)
        Exit Function
    End If
    With Target
        'get the last non-blank row including hidden cells (if any)
        nLast = .Find(What:="*", After:=.Cells(1), LookAt:=xlPart, _
            LookIn:=xlFormulas, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=False).Row
        'get the number of cells in Target from first to nLast
        nCells = nLast - .Cells(1).Row + 1
        'get the last 5 cells but 1 as a range of cells
        Set rLast5But1 = .Cells(nCells - 5).Resize(5)
        'WorksheetFunction.Average ignores non-numeric cells
        AvgLast5But1 = WorksheetFunction.Average(rLast5But1)
    End With
End Function

Use it in a cell formula like this
    =AvgLast5But1(A:A)
or like this
    =AvgLast5But1(A5:A999)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.