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

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.