## Explanation

In the example shown, we have a list of amounts in column C. The goal is to dynamically sum the last **n** amounts using the number that appears in cell E5 for **n**. Since the list may grow over time, the key requirement is to sum amounts *by ** position.* For convenience only, the values to sum 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 older versions of Excel, you can use the OFFSET function. Both approaches are explained below.

### TAKE function

The TAKE function returns a subset of a given array. 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 sum the last **n** values that appear in **data**, where the number of rows to return is a variable entered in cell E5. To retrieve these values, we can use TAKE like this:

`TAKE(data,-E5)`

With the number 6 in cell E5, TAKE will return an array with six values like this:

`{1050;1150;1250;1175;1350;1325}`

To sum these values, we simply need to nest the TAKE function inside the SUM function:

```
=SUM(TAKE(data,-E5))
=SUM({1050;1150;1250;1175;1350;1325})
=7300
```

The result is 7,300, the sum of the last six values in the range C5:C16. As new values are added to **data**, TAKE will continue to return the last **n** values to SUM.

### OFFSET function

In older versions of Excel that do not have the TAKE function, you can use the OFFSET function to solve this problem. OFFSET is designed to create a reference for 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. To sum the last 6 values in **data** (C5:C16), we can use the OFFSET function with the SUM function like this:

`=SUM(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. For *cols*, we provide 0 since we don't want 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*. In this configuration, OFFSET will return a reference to C11:C16, which contains the last 6 values in **data**. The formula will evaluate like this:

```
=SUM(OFFSET(C5,COUNT(data)-E5,0,E5))
=SUM(OFFSET(C5,12-6,0,6))
=SUM(C11:C16)
=SUM({1050;1150;1250;1175;1350;1325})
=7300
```

The final result is 7300, the sum of the last six 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:

`=SUM(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.