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.

Making the range dynamic

One limitation of the formula explained above is that it assumes that the range going into the TAKE function contains all data. What should you do if new data is being added on an ongoing basis? One easy solution is to use the TRIMRANGE function to create a simple dynamic range like this:

=AVERAGE(TRIMRANGE(TAKE(range,-n)))

The TRIMRANGE function removes empty rows and columns from a range of data. The result is a "trimmed" range that only includes data from the used portion of the range. Because it is a formula, TRIMRANGE will update the range dynamically when data is added or removed from the original range. To make this work correctly, you will want to use a range that is largest enough to hold all possible data. For example, if the data is in column A, you can use a full-column reference like this:

=AVERAGE(TRIMRANGE(TAKE(A:A,-n)))

With TRIMRANGE in the formula, TAKE will always get the latest data. You can read more about the TRIMRANGE function here.

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.

Making the range dynamic

One limitation of the formulas above is that they won't automatically include new data added to the range. In an older version of Excel, one way to make the range expand to include new data is to create a dynamic named range with a formula. You can create a dynamic named range with the OFFSET function or with the INDEX function. Another (simpler) option is to use an Excel Table to create the range.