Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

3 Handy Ways to Average Every Nth Row in Excel


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.
=AVERAGE(OFFSET($C$5,(ROW()-ROW($D$5))*3,,3,))

Combining ROW, OFFSET, And AVERAGE Functions as A Handy Way to Average Every Nth Row in Excel

Formula Breakdown

  • OFFSET($C$5,(ROW()-ROW($D$5))*3,,3,): The OFFSET function with the help of the ROW function will fetch the data starting from cell C5 for calculation. Here, the row height is 3 so this function will collect three rows of height downward starting from cell C5.
  • AVERAGE(OFFSET($C$5,(ROW()-ROW($D$5))*5,,5,)): Finally, the AVERAGE function will calculate the average of the values, found from the previous steps.

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.
=AVERAGE(INDEX(C:C,5+3*(ROW()-ROW($D$5))):INDEX(C:C,4+3*(ROW()-ROW($D$5)+1)))

Merging AVERAGE, INDEX, And ROW Functions as A Handy Way to Average Every Nth Row in Excel

Formula Breakdown

  • INDEX(C:C,5+3*(ROW()-ROW($D$5))): The INDEX function will find the value from the cell range or column C that falls in row 5. The row number is calculated using the ROW function.
  • INDEX(C:C,4+3*(ROW()-ROW($D$5)+1)): Similarly, the INDEX function will find the value from the third row of the data set, which is row 9 in the worksheet.
  • AVERAGE(INDEX(C:C,5+3*(ROW()-ROW($D$5))):INDEX(C:C,4+3*(ROW()-ROW($D$5)+1))): Finally, the AVERAGE function will calculate the average of these two-row ranges found in the previous two sections.

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


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.

Selecting Developer Tab for Applying VBA as A Handy Way to Average Every Nth Row in Excel

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

Writing Code for Applying VBA as A Handy Way to Average Every Nth Row in Excel

VBA Breakdown

  • Firstly, naming the sub-procedure.
Sub Average_Row_Range()
  • Secondly, declare the variables.
Dim x As Integer
  • Thirdly, assign the desired drow range for calculating the average.
x = WorksheetFunction.Average(Range("C5:C9"))
  • Finally, use a message box to display the result.
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.

Showing Final Resut for Applying VBA as A Handy Way to Average Every Nth Row in Excel

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.
=MOD(ROW(C5)-4,5)=0

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.
=SUM(C5:C29*(MOD(ROW(C5:C29)-1,3)=0))

Using SUM, MOD, and ROW Functions as A Easy Way to Sum Every Nth Row in Excel

Formula Breakdown

  • MOD(ROW(C5:C29)-1,3)=0: The MOD function will return 0 for every third row in the cell range C5:C29. As the data set does not start from cell A1, to find out the correct value of the row for this cell range, -1 has been used in the formula.
  • SUM(C5:C29*(MOD(ROW(C5:C29)-1,3)=0)): Finally, the SUM function will sum up values from the rows that are true for the MOD function formula.

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.


Md. Araf Bin Jayed

Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo