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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel Data Analysis and Business Modeling today!
Trying to calculate how much people owe you? If you charge interest or service charges on past-due accounts, there are a ...
Discover MoreWhen you copy a formula from one cell to another, Excel normally adjusts the cell references within the formula so they ...
Discover MoreWhen processing some text data, you may need to perform some esoteric function, such as adding dashes between letters. ...
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