You can use AVERAGE, OFFSET, and COUNT functions to average last 5 values in columns.

Generic Formula

**=AVERAGE(OFFSET(first_cell,COUNT(range)-5,0,5))**

**Range**: Required. The range that you want to take 5 last values from it

**First_cell**: Required. The first cell of the range

The OFFSET function is used here to return an array of a range with rows, columns, height, and weight. The AVERAGE function then averages the elements in the array.

## Example:

Considering the example below. The formula in G5 is **=AVERAGE(OFFSET(B4,COUNT(B4:B10)-5,0,5))**

*Figure 1 – Average last 5 values in columns*

## Explanation

OFFSET function has syntax like this: OFFSET(reference, rows, cols, [height], [width])

In this case, this is how ** OFFSET(B4,COUNT(B4:B10)-5,0,5) **portion works:

- Reference: It bases the offset from
**B4** - Rows: COUNT function counts the number of rows in range
**B4:B10**, which is 7 rows. It then**subtracts 5**because we want the offset to go down 2 row from the base, leave the last 5 rows for the returned range. - Cols: is 0 because we want the offset to stay in the same column
- [height]: is 5 because we want the returned range with 5 rows.
- [width]: is omitted here. It defaults 1, which means the returned range with 1 column.

OFFSET function returns an array like this {14;21;8;7;7}

Finally, AVERAGE function averages the array and returns **11.4**

Extending the formula through column G to J to get other results:

*Figure 2 – Average last 5 values in columns*

If your data is in a horizontal arrangement, you can change the formula a little bit to adapt to the same works.

*Figure 3 – Average last 5 values in columns*

**OFFSET(B3,0,COUNT(B3:H3)-5,1,5):**

- Reference: It bases the offset from
**B3** - Rows: 0 because we want to stay in the same row
- Cols: COUNT function counts the number of columns in range
**B3:H3**, which is 7 columns. It then**subtracts 5**because we want the offset to go right 2 columns from the base, leave the last 5 columns for the returned range - [height]: is 1 because we want the returned range with 1 rows. It cannot be omitted like the previous case because you need to fill this argument before giving the next argument.
- [width]: is 5 which means the returned range with 5 columns.

If you want to average N last values, replace number **5** with number **N** in the main formula.

## Leave a Comment