## Explanation

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.