How to Average Every Nth Row in Excel (With Example)

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:

Excel average every nth row

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:

The average of these values can be calculated as:

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.

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