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