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
Excellent. One question if I may. I want to average the last 3 entries in a row where some cells have no entries. How do I do that?
Hi Peter…To calculate the average of the last 3 non-empty entries in a row in Excel, you can use a combination of functions such as `FILTER`, `INDEX`, `LARGE`, and `AVERAGE`. Here’s a step-by-step method:
—
### **Formula for Averaging the Last 3 Non-Empty Values**
Assume your data is in **row 1** (e.g., from cell A1 to Z1).
1. **Using a Dynamic Formula (Modern Excel with `FILTER`):**
If your Excel version supports `FILTER` (Office 365 or Excel 2021):
“`excel
=AVERAGE(FILTER(A1:Z1, A1:Z1<>“”)[-3:])
“`
– `FILTER(A1:Z1, A1:Z1<>“”)`: Filters out non-empty cells.
– `[-3:]`: Selects the last 3 values.
– `AVERAGE(…)`: Calculates the average of the last 3 filtered values.
2. **Using an Array Formula for Older Versions (with `LARGE`):**
For Excel versions without `FILTER`:
“`excel
=AVERAGE(LARGE(IF(A1:Z1<>“”, COLUMN(A1:Z1)-MIN(COLUMN(A1:Z1))+1), {1,2,3}))
“`
– **Steps**:
– `IF(A1:Z1<>“”, A1:Z1)`: Creates an array of non-empty values.
– `LARGE(…, {1,2,3})`: Extracts the last 3 largest (most recent) values.
– `AVERAGE(…)`: Averages the extracted values.
– **Important Note:** This is an **array formula**, so press **Ctrl + Shift + Enter** after typing it to make it work in older Excel versions.
—
### Example Walkthrough
If your row contains:
| A1 | B1 | C1 | D1 | E1 | F1 |
|—–|—–|—–|—–|—–|—–|
| 5 | | 8 | 10 | | 6 |
– The last 3 non-empty values are `8`, `10`, and `6`.
– The formula calculates:
\[
\text{Average} = \frac{8 + 10 + 6}{3} = 8
\]
—