Excel: Calculate Average of Last N Values in Row or Column

You can use the following formulas in Excel to calculate the average of the last n values in a row or column:

Formula 1: Calculate Average of Last N Values in Column

=AVERAGE(OFFSET(A2,COUNT(A2:A11)-5,0,5))

This particular formula calculates the average of the last 5 values in the column range A2:A11.

Formula 2: Calculate Average of Last N Values in Row

=AVERAGE(OFFSET(A2,0,COUNT(B1:F1)-1,,-3))

This particular formula calculates the average of the last 3 values in the row range B1:F1.

The following examples show how to use each formula in practice.

Example 1: Calculate Average of Last N Values in Column

Suppose we would like to calculate the average of the last 5 values in column A:

We can type the following formula into cell C2 to do so:

=AVERAGE(OFFSET(A2,COUNT(A2:A11)-5,0,5))

The following screenshot shows how to use this formula in practice:

We can see that the average of the last 5 values in the range A2:A11 is 22.6.

We can verify this is correct by manually calculating the average of the last 5 values in this range:

Average = (44 + 28 + 17 + 14 + 10) / 5 = 22.6

This matches the value calculated by our formula.

To calculate the average of a different number of last values in the column, simply replace the 5‘s in the formula with a different number.

For example, we can calculate the average of the last 3 values in the range by using the following formula:

=AVERAGE(OFFSET(A2,COUNT(A2:A11)-3,0,3))

Suppose we would like to calculate the average of the last 3 values in the first row of this Excel sheet:

We can type the following formula into cell B3 to do so:

=AVERAGE(OFFSET(B1,0,COUNT(B1:F1)-1,,-3))

The following screenshot shows how to use this formula in practice:

Excel average of last n values in row

We can see that the average of the last 3 values in the range B1:F1 is 24.33.

We can verify this is correct by manually calculating the average of the last 3 values in this range:

Average = (40 + 16 + 17) / 3 = 24.33

This matches the value calculated by our formula.

To calculate the average of a different number of last values in the column, simply replace the 3 in the formula with a different number.

For example, we can calculate the average of the last 4 values in the range by using the following formula:

=AVERAGE(OFFSET(B1,0,COUNT(B1:F1)-1,,-4))

The following tutorials explain how to perform other common operations in Excel: