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**,

**and**

*OFFSET,***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.**

*AVERAGE***Step 1:**

- First of all, I will calculate the average of the data in the
column for every three rows.*Sales* - 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,)*with the help of*The OFFSET function**the*will fetch the data starting from cell*ROW function*for calculation. Here, the row height is 3 so this function will collect three rows of height downward starting from cell*C5*.*C5*: Finally,*AVERAGE(OFFSET($C$5,(ROW()-ROW($D$5))*5,,5,))*will calculate the average of the values, found from the previous steps.*the AVERAGE function*

**Step 2:**

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

**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**,

**and**

*INDEX,***functions of Excel. To learn more about this procedure, go through the following steps.**

*ROW***Step 1:**

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

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

**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
as an error.*#DIV/0!*

**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
tab of the ribbon.*Developer* - Then, from the
group, select*Code*.*Visual Basic*

**Step 2:**

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

**Step 3:**

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

```
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()`

- 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
or the*F5*button to run the code.*Run*

**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**,

**, and**

*MOD***functions. The steps for determining the sum are given below.**

*ROW***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
to sum up every third row and show the result.*E5*

`=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 cell range. As the data set does not start from cell*C5:C29*, to find out the correct value of the row for this cell range,*A1*has been used in the formula.*-1*: Finally,*SUM(C5:C29*(MOD(ROW(C5:C29)-1,3)=0))*will sum up values from the rows that are true for*the SUM function*formula.*the MOD function*

**Step 4:**

- Fourthly, press
and see the desired result in cell*Enter*.*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.

## Related Articles

**How to Calculate Daily Average from Hourly Data in Excel****Calculate Average Only for Cells with Values in Excel****How to Calculate Average of Multiple Ranges in Excel (3 Methods)****Calculate Average of Multiple Columns in Excel (6 Methods)****How to Calculate Average of Text in Excel (2 Ways)****Calculate Moving Average for Dynamic Range in Excel (3 Examples)****How to Calculate Average Percentage Increase in Excel**