How to Average Every Nth Row in Excel (3 Ways)

Sometimes, while working in Excel, users need to average cell values for working purposes. While working in a big data set, doing the average of cells one by one is a tiresome and tedious process. The process will become much easier if users can average every nth row by just using one formula or command. In this article, I will show you how to average every nth row in Excel.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

3 Handy Ways to Average Every Nth Row in Excel

In this article, you will see three different ways to average every nth row in Excel. First of all, I will combine some Excel functions to calculate the average. Then, in the second approach, I will merge some functions of Excel for the same purpose. Lastly, I will use a VBA code to calculate the average of a particular row range.

To explain my article further, I will use the following sample data set. Here, I have the random data for sales for the twenty-five days of a company.

1. Combining Functions to Average Every Nth Row

In the first method, I will combine some Excel functions to average every nth row in Excel. In order to do that, I will combine the ROW, OFFSET, and AVERAGE functions of Excel. I will explain the role of each of these functions in the calculation of this discussion. See the below-given steps to learn more about this procedure.

Step 1:

• First of all, I will calculate the average of the data in the Sales column for every three rows.
• In order to do that, write the following formula in cell D5.

Step 2:

• Secondly, press Enter to see the average of the first three rows in cell D5.
• Then, use AutoFill to drag the formula to the lower cells of this column.
• Here, you will see a numerical value, till the formula will find values for calculations.
• After a certain period, it will show #DIV/0! as an error.

Step 3:

• Consequently, modify the formula a little bit and you will see the average for every five rows.

Read More: How to Calculate Average in Excel (Including All Criteria)

2. Merging Functions to Average Every Nth Row

In my second approach, I will merge different functions of Excel to find the average. In this merge, I will use the AVERAGE, INDEX, and ROW functions of Excel. To learn more about this procedure, go through the following steps.

Step 1:

• Firstly, I will determine the average sales for every three rows.
• To do that, in cell D5, use the following merging formula.

Step 2:

• Secondly, to find out the average of the first three rows, press Enter.
• Then, drag the same formula to the lower cells of the column by using the Fill Handle.
• Consequently, the formula will show numerical values based on the size of your data set.
• After a certain period, it will show #DIV/0! as an error.

Step 3:

• Furthermore, to get the average for every five rows with the same formula, you have to modify the formula a little bit.

Read More: How to Average a Column in Excel (7 Easy Methods)

Similar Readings

• How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)
• [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
• How to Calculate Average and Standard Deviation in Excel
• Calculate Average Percentage Change in Excel (3 Simple Ways)
• How to Calculate Average in Excel Excluding 0 (2 Methods)

3. Applying VBA to Average Nth Row in Excel

As the last procedure of this task, I will apply a VBA code. By giving proper sequence and command into the code, I will determine the average of a fixed cell range. How I will do it is shown in the following steps.

Step 1:

• First of all, go to the Developer tab of the ribbon.
• Then, from the Code group, select Visual Basic.

Step 2:

• Secondly, the Visual Basic window will appear after the previous step.
• Then, from the Insert tab choose Module.

Step 3:

• Thirdly, copy the following code into the module.
• Here, I have entered C5:C7 as the cell range to calculate the average that represents the first three rows of the data set.

Sub Average_Row_Range()
Dim x As Integer
'Assign the row range for calculating average
x = WorksheetFunction.Average(Range("C5:C9"))
'Displaying the result in a msgbox
MsgBox "The average of this row range is " & x
End Sub

Sub Average_Row_Range()

Dim x As Integer

x = WorksheetFunction.Average(Range("C5:C9"))

MsgBox "The average of this row range is " & x

Step 4:

• Fourthly, save the code, and then after keeping the cursor in the module press F5 or the Run button to run the code.

Step 5:

• Finally, you will see the average of the row range as a result after running the code.

Step 6:

• Consequently, change the cell range in the code, and you will see the average for the first five rows in the message box.

Read More: Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)

Sum Every Nth Row in Excel

After discussing how to average every nth row in Excel, we will now see a simple procedure to sum every nth row in Excel. To do this, we will have to use three Excel functions that are- SUM, MOD, and ROW functions. The steps for determining the sum are given below.

Step 1:

• Firstly, for better understanding, I will show the row numbers that will be summed up by those Excel functions.
• In order to do that, insert the following formula into cell D5.

Step 2:

• Secondly, as I want to sum every third row from the data set, by using the above formula, you can see which row will be added to the main formula.

Step 3:

• Thirdly, enter the following formula in cell E5 to sum up every third row and show the result.

Step 4:

• Fourthly, press Enter and see the desired result in cell E5.

Step 5:

• Moreover, by modifying the above formula, you can determine the sum of every five rows from the data set.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to average every nth row in Excel. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.