# 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:

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:

## One Reply to “How to Average Every Nth Row in Excel (With Example)”

1. Gary says:

Appreciate your initial guidance here, but can’t seem to get it to work in my effort.
I have a spreadsheet of scores (running from Row 193 thru Row 817 — and growing) for which I’m trying to determine a running cumulative average for a particular score — an average of 7 days’ scores — (by averaging the ‘averages’ that I calculate for and place in every 12th row).
I keep getting a ‘zero’ (0) instead of a real number when I attempt the ‘average-if-mod-row’ approach. (Hope that’s clear, since I can’t paste a snippet grab.) I’m using an absolute for Row193 since I want to be able to copy and paste the formula as the table continues to grow.

Here’s the formula (Excel 365) I’ve tried in cell O815:
=AVERAGE(IF(MOD(ROW(O\$193:O803)-MIN(ROW(O\$193:O803)),12)=0,O\$193:O803))

Can you show me where I’m making my error? Thanks.