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.

Additional Resources

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

How to Select Every Nth Row in Excel
How to Sum Every Nth Row in Excel
How to Select Every Other Column in Excel

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

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *