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.
Leave a Comment