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
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.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Using a formula to find information in a text value is easy. Using a formula to find either of two text values within a ...
Discover MoreExcel can be used as a simple database program. If you use asterisks in a column of your database to designate ranking of ...
Discover MoreReplacing one character in a text value with another character is easy. All you need to do is use the SUBSTITUTE ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments