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.
How to Average Every Nth Row in Excel: 3 Ways
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.
Steps:
- 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,))
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.
- Next, 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 finds values for calculations.
- After a certain period, it will show #DIV/0! as an error.
- Now, modify the formula a little bit and you will see the average for every five rows.
Read More: How to Average a Column in Excel
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 combination of AVERAGE, INDEX, and ROW functions of Excel.
Steps:
- 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)))
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.
- Press Enter to find out the average of the first three rows.
- 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.
- Furthermore, to get the average for every five rows with the same formula, you have to modify the formula a little bit.
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.
Steps:
- First of all, go to the Developer tab of the ribbon.
- From the Code group, select Visual Basic.
- Next, the Visual Basic window will appear after the previous step.
- Then, from the Insert tab choose Module.
- Copy the following code into the module.
- Here, I have entered C5:C7 as the 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
VBA Breakdown
- Firstly, naming the sub-procedure.
Sub Average_Row_Range()
- Then, declare the variables.
Dim x As Integer
- 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
- Save the code, and then after keeping the cursor in the module press F5 or the Run button to run the code.
- Finally, you will see the average of the row range as a result after running the code.
- 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
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.
Steps:
- Firstly, 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
- 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.
- 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))
Formula Breakdown
- MOD(ROW(C5:C29)-1,3)=0: The MOD function will return 0 for every third row in the 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.
- Press Enter and see the desired result in cell E5.
- Moreover, by modifying the above formula, you can determine the sum of every five rows from the data set.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
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.
Related Articles
- How to Calculate Average of Multiple Columns in Excel
- How to Calculate Average of Multiple Ranges in Excel
- How to Calculate Average Only for Cells with Values in Excel
- How to Find Average with Blank Cells in Excel
- How to Average Only Visible Cells in Excel
- How to Find Average of Specific Cells in Excel
- How to Exclude a Cell in Excel AVERAGE Formula
- How to Fix Divide by Zero Error for Average Calculation in Excel
- How to Ignore #N/A Error When Getting Average in Excel
- [Fixed!] AVERAGE Formula Not Working in Excel