Average last n rows

In the worksheet shown, we have a list of values in column C. The goal is to dynamically average the last n values using the numbers in cell E5 for n. Since the list may grow over time, the key requirement is to average amounts by position. For convenience only, the values to average are in the named range data (C5:C16). In the latest version of Excel, the best way to solve this problem is with the TAKE function, a new dynamic array function in Excel. In Legacy Excel, you can use the OFFSET function. Both approaches are explained below.

TAKE function

The TAKE function returns a subset of a given array, where the number of rows and columns to return is provided by separate rows and columns arguments. For example, you can use TAKE to return the first 3 rows or columns of an array like this:

=TAKE(array,3) // first 3 rows
=TAKE(array,,3) // first 3 columns

A great feature of TAKE is that you can supply a negative value to retrieve last rows or columns:

=TAKE(array,-3) // last 3 rows
=TAKE(array,,-3) // last 3 columns

In this problem, we want to average the last n values that appear in data, where the number of rows to return is a variable entered in cell E5. To accomplish this, we can use TAKE like this:

TAKE(data,-E5)

Note we are changing the value in cell E5 to a negative number in the formula. With the number 5 in cell E5, TAKE will return an array with five values like this:

{1150;1250;1175;1350;1325}

To average these values, we simply need to nest the TAKE function inside the AVERAGE function like this:

=AVERAGE(TAKE(data,-E5))
=AVERAGE({1150;1250;1175;1350;1325})
=1250

The result is 1250, the average of the last five values in the range C5:C16. As new values are added to data, TAKE will continue to return the last n values to AVERAGE, so the formula will continue to return the correct result.

OFFSET function

In older versions of Excel without the TAKE function, you can use the OFFSET function to solve this problem. OFFSET is designed to create a reference to a range using five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns. In this case, we need to configure OFFSET to build a range to the last n values in data (C5:C16). To do that, we use OFFSET with the COUNT function like this:

=AVERAGE(OFFSET(C5,COUNT(data)-E5,0,E5))

Inside OFFSET, we use C5 for reference, since we want to start at C5. For rows, we use this snippet:

COUNT(data)-E5 // rows offset

Our goal is to create a range that starts at the cell in data that is n cells before last cell. The COUNT function returns the number of numeric values in data. We subtract E5 to "back up" to the correct cell at the start of the range. For cols, we provide 0 since we only have one column of values and don't need a column offset. We provide E5 for height, since we want our final range to be n cells tall. We don't need to provide a value for the optional width argument, since width will inherit from reference, which is 1 column wide. In this configuration, OFFSET will return a reference to C11:C16, which contains the last 5 values in data. The formula will evaluate like this:

=AVERAGE(OFFSET(C5,COUNT(data)-E5,0,E5))
=AVERAGE(OFFSET(C5,12-5,0,5))
=AVERAGE(C12:C16)
=AVERAGE({1150;1250;1175;1350;1325})
=1250

The final result is 1250, the average of the last five values in the range C5:C16.

INDEX function

One thing you might notice about the OFFSET formula above is that we are providing a reference to both data and cell C5, the first cell in data. This makes the formula more error-prone since data and C5 are disconnected. You can make the formula more robust (and portable) by using the INDEX function to return the first cell in data like this:

=AVERAGE(OFFSET(INDEX(data,1),COUNT(data)-E5,0,E5))

This works because the INDEX function returns C5 as a reference, not a value. Now as long as the reference to data is correct, the formula will work properly.