Average last n columns

In this example, the goal is to average the last n columns in a set of data, where n is a variable entered in cell K5 that can be changed at any time. Since more data may be added, a key requirement is to average amounts by position. For convenience, the values to average are in the named range data (C5:H16). 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 or the INDEX function. All three approaches are explained below.

TAKE function

The TAKE function returns a subset of a given array or range. The size of the array returned is determined by separate rows and columns arguments:

=TAKE(array,rows,columns)

When positive numbers are provided for rows or columns, TAKE returns values from the start of the array:

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

When negative numbers are provided, TAKE returns values from the end of the array:

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

In the worksheet shown, data is the named range C5:H16. We can retrieve the last 3 columns like this:

=TAKE(data,,-3) // last 3 columns

Notice we simply omit rows in this case because we want all rows in data. To make the number of columns variable, we simply swap in the reference to J5 and add a negative sign:

=TAKE(data,,-J5)

Finally, to average the result from TAKE, we nest the TAKE function inside the AVERAGE function:

=AVERAGE(TAKE(data,,-J5))

With 3 in cell J5, TAKE returns the last 3 columns in data. This result is handed off to the AVERAGE function, which returns a final result of 8.42, the average of values in the range F5:H16.

OFFSET function

In older versions of Excel, another way to solve this problem is to use the OFFSET function. The OFFSET function returns a reference to a range constructed with 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 average the last 3 columns in the named range data, we can use the OFFSET function like this:

=AVERAGE(OFFSET(data,0,COLUMNS(data)-J5,,J5))

Inside the OFFSET function, we provide data for reference and 0 for rows, since we don't want any row offset. Next, we count the number of columns in data with the COLUMNS function and subtract the value for n in cell J5 to get a value for cols, the column offset. We leave height empty, because OFFSET will automatically inherit the height of reference, and we supply J5 for width since we want a 3-column range in the end. In this configuration, OFFSET returns a 3-column range starting at cell F5 in data containing all 12 rows.

Note: the OFFSET function is a volatile function and can cause performance problems in larger or more complicated worksheets. If you run into this problem, see the INDEX solution below.

INDEX function

Another way to solve this problem is to use the versatile INDEX function in a formula like this:

=AVERAGE(INDEX(data,0,COLUMNS(data)-(J5-1)):INDEX(data,0,COLUMNS(data)))

The key to understanding this formula is to realize that the INDEX function can return a reference to entire rows and entire columns. To generate a reference to the "last n columns" in a table, we build a reference in two parts: the left column and the right column, then use the range operator (:) to join the two parts together:

=AVERAGE(left:right)

To get a reference to the left column, we use:

INDEX(data,0,COLUMNS(data)-(J5-1))

Since data contains 6 columns, the COLUMNS function returns 6, and this simplifies to:

INDEX(data,0,4) // column 4

INDEX returns a reference to column 4, F5:F16. For the right column in the range, we use INDEX like this:

INDEX(data,0,COLUMNS(data))

Since COLUMNS returns 6, this simplifies to:

INDEX(data,0,6) // column 6

INDEX returns a reference to column 6, H5:H16. Together, the two INDEX functions return a reference to columns 4 through 6 in the data (i.e. F5:H16). The range operator (:) joins the two references together, and the AVERAGE function returns a final result of 8.42.