Get instant live expert help with Excel or Google Sheets “My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# Average last 5 values in columns

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.

Are you still looking for help with the Average function? View our comprehensive round-up of Average function tutorials here.

### Did this post not answer your question? Get a solution from connecting with the expert. 