< Go Back

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar