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))

**Example 2: Calculate Average of Last N Values in Row**

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:

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))

**Additional Resources**

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

How to Calculate Average and Drop Lowest Value in Excel

How to Calculate Average of Top N values in Excel

How to Average Every Nth Row in Excel