Table of Contents
The average of every nth row in Excel is a useful tool for data analysis. This technique can be used to quickly calculate a rolling average of any data set. By taking the average of every Nth row, one can identify trends and patterns in the data that may not be apparent using other methods. For example, if one is looking to identify a trend in a stock’s closing price, they can take the average of every 5th day’s closing price. This will result in a smoother curve, with less noise than if the daily closing prices were used. This technique can also be used to quickly identify data points that are outliers, as they will be more easily identifiable when compared to the average of every Nth row.
You can use the following basic formula to average every nth row in Excel:
=AVERAGE(IF(MOD(ROW(A2:A21)-MIN(ROW(A2:A21)),n)=0,A2:A21))
This formula calculates the average of every nth value in the range A2:A21.
Simply change the value for n in the formula to average specific rows.
For example, you can use the following formula to average every 4th row:
=AVERAGE(IF(MOD(ROW(A2:A21)-MIN(ROW(A2:A21)),4)=0,A2:A21))
The following examples show how to use this formula in practice.
Example: Average Every Nth Row in Excel
Suppose we have the following column of values in Excel:
We can use the following formula to calculate the average of every 4th row from column A:
=AVERAGE(IF(MOD(ROW(A2:A21)-MIN(ROW(A2:A21)),4)=0,A2:A21))
The following screenshot shows how to use this formula in practice:
The formula shows that the average of every 4th row in the range A2:A21 is 18.
We can verify this is correct by manually identifying each 4th value in the range:
Average = (4 + 10 + 17 + 29 + 30) / 5 = 18
If we change the value of n in the formula, we can select a different nth value.
For example, we can use the following formula to calculate the average of every 6th row in the range:
=AVERAGE(IF(MOD(ROW(A2:A21)-MIN(ROW(A2:A21)),6)=0,A2:A21))
The following screenshot shows how to use this formula in practice:
The formula shows that the average of every 6th row in the range A2:A21 is 18.25.
We can verify this is correct by manually identifying each 6th value in the range:
The average of these values can be calculated as:
Average = (4 + 15 + 29 + 25) / 4 = 18.25
This matches the value calculated by our formula.