Table of Contents
To calculate the average of every Nth row in Excel, you can use the AVERAGEIFS function. This function allows you to specify the range of cells to be averaged and the criteria for selecting which rows to include. For example, if you want to find the average of every 5th row in a column, you would use the formula =AVERAGEIFS(range, criteria, 5). This will calculate the average of all cells in the specified range that meet the criteria of being in the 5th row. An example of this would be finding the average of every 5th sales transaction in a large dataset. By using the AVERAGEIFS function, you can easily and accurately calculate the average of every Nth row in Excel.
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:
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: