Table of Contents
Calculating the average of the last N values in a row or column using Excel can be done using a simple formula. First, select the cell where you want the average to appear. Then, use the AVERAGE function followed by the range of cells containing the last N values. This will provide the average of the specified range, helping you to quickly and accurately calculate the average of the last N values in a row or column. Excel’s built-in functions make this process quick and efficient, allowing you to easily analyze and summarize data in a spreadsheet.
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:
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: