# How to Average Every Nth Cell in Excel [3 Ways]

Averaging every nth cell in Microsoft Excel becomes easier than ever if you use the methods outlined in this simple Excel guide.

When analyzing data in Excel, you often get the task to derive an average of sales, revenues, expenses, scores, and so on. It’s ridiculously easy to use the AVERAGE function in Excel to get a quick mean of the values highlighted.

However, if you need to get an average of every four, five, seven, etc., the AVERAGE function won’t work. You must know how to average every nth cell in Excel. Keep reading to find all the popular methods below.

## Reasons to Learn Average Every Nth Cell in Excel

Find below some common reasons to learn the ways to average every nth cell in Excel:

- If you have a large dataset, averaging every nth cell using Excel formulas and scripts can save you a lot of time by automating the process.
- It gives you enhanced insight into the collected data by creating average values in groups. For example, you can create averages based on team members, dates, months, departments, and so on.
- By averaging every Nth cell, you can identify patterns, anomalies, and trends in your data more effectively. Thus, you or your managers can make informed decisions based on such insights.
- Averaging specific intervals can simplify complex datasets. Then, it becomes easier to create meaningful charts and graphs to effectively communicate your findings.
- You have the flexibility to choose the interval (N) that suits your analysis requirements, allowing you to zoom in or out on specific sections of your data.
- Your organization or employer may ask for this Excel skill. It’s particularly important if you’re working in finance, banking, data research, marketing, sales, etc., roles.

Let’s explore below various ways to get a mean value of data after every nth cell using methods like formulas, VBA scripts, etc.

## Using the AVERAGE Function of Excel

Here’s how you can use the **AVERAGE**, **OFFSET**, and **ROW** functions in an array to average every nth cell in Excel:

- Open your Excel worksheet and highlight the column or row containing the data you want to average.
- Determine the interval or “
**N**” value. It represents how many cells you want to skip before calculating the average. For example, if you want to average every 4th cell, N would be**4**. - Select an empty cell where you want to display the average result.
- In that empty cell, enter the following formula:

`=AVERAGE(OFFSET($E$2,(ROW()-ROW($F$2))*4,,4,))`

- In the above formula, you must make some changes to the cell ranges and references to get the correct values. For example, if the values are under
**column E**, and**E1**contains the header row, then the first row is`$E$2`

. - Similarly, you also need to designate the cell where you want to get the average of every nth cell. In the present example, that’d be cell
**F2**or`$F$2`

. - The current formula derives the average of every four cells under
**column E**. Hence, you see`*4,,4`

in the formula. - If you need an average of every three or five cells, you need to enter *
`3,,3`

and`*5,,5`

in place of`*4,,4`

.

- Now, simply hit the
`Enter`key to get the average of the first four cells under**column E**.

- Drag the fill handle until the end of the column (where data ends) to generate the mean values of every 4th cell automatically.
- When you start seeing the
**#DIV/0!**, it means no data left to calculate the mean by a group of four cells.

## Use These VBA Scripts

Find below an effortless VBA code to automatically fetch the average of every nth row in Excel without creating any formulas. However, you’ll need to manually change the cell range within the VBA code to get the mean values. For example, if you need an average after every four cells, you need to enter the cell range like `E2:E5`

. Here’s how it works:

- Click
**File**and choose**Save As**on the left-side panel that appears. - Then, choose
**Browse**and select a folder like**Downloads**. - The
**Save As**dialog box will pop up. - There, click the
**Save as type**dropdown menu and choose the**Excel Macro-Enabled Workbook**option. - Click the
**Save**button to save the file.

So far, you created an **XLSM** file to run VBA scripts because XLSX files don’t run VBA or Macro scripts. Let’s find the code and how to run it in these steps:

- Hit
`Alt`+`F11`to bring up the**VBA Editor**tool. - Click the
**Insert**button on the VBA toolbar and select**Module**. - A new blank Module will show up on the
**VBA Editor**backstage (the grey board). - There, copy and paste the following VBA code:

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

- In the above script, change the cell range
`E2:E5`

according to your Excel worksheet. For example, if all the values are under**column E**, use`E2:E4`

for the average of every three cells. - Click
**Save**on the toolbar and close the tool.

- Hit
`Alt`+`F8`to bring up the**Macro**dialog box and select**Average_Row_Range**. - Click
**Run**to get average values. - You must edit the range each time to get the average values of different groups of cells.

If you feel that the above VBA script is too manual, then you can use the following VBA code:

```
Sub AverageEveryNthCell()
Dim rangeToAverage As Range
Dim N As Integer
Dim i As Integer
Dim sum As Double
Dim count As Integer
' Set the range to average
Set rangeToAverage = Range("A1:A10") ' Replace with your desired range
' Set the interval or N value
N = 3 ' Replace with your desired interval
' Initialize variables
sum = 0
count = 0
' Loop through the cells and calculate the average
For i = 1 To rangeToAverage.Cells.Count
If (i Mod N) = 0 Then
sum = sum + rangeToAverage.Cells(i).Value
count = count + 1
End If
Next i
' Calculate and display the average
If count > 0 Then
MsgBox "Average of every " & N & "th cell: " & sum / count
Else
MsgBox "No cells found for averaging."
End If
End Sub
```

Don’t forget to customize the code in the following code elements. The steps to execute the code are similar to the previous VBA script.

`A1:A10`

: is the cell range where the data exists.`N=3`

: it’s every nth cell that you want to average.

## Use An Office Scripts Code

If you need advanced automation to average every nth cell in Excel, you can use this Office Scripts code on Excel Automate. The code is valid for both Excel 365 desktop app and Excel on the web app. This method is particularly helpful if you need to automatically fetch the mean of every nth cell on Excel online. Here’s the code and how to set it up:

- Open the Excel file where you have got values for which you need averages after certain intervals.
- Click the
**Automate**tab on the ribbon menu and then select the**New Script**button inside the**Scripting Tools**command block.

- You should now see the
**Code Editor**on the right side of Excel. - Copy and paste the following script into the code editor:

```
function main(workbook: ExcelScript.Workbook) {
let worksheet = workbook.getActiveWorksheet();
// Set the range to average
let rangeToAverage = worksheet.getRange("E2:E21"); // Replace with your desired range
// Set the interval or N value
let N = 3; // Replace with your desired interval
// Initialize variables
let sum = 0;
let count = 0;
// Loop through the cells and calculate the average
for (let i = 0; i < rangeToAverage.getCellCount(); i++) {
if ((i + 1) % N === 0) {
sum += rangeToAverage.getCell(i).getValue();
count++;
}
}
// Calculate and display the average
if (count > 0) {
console.log(`Average of every ${N}th cell: ${sum / count}`);
} else {
console.log("No cells found for averaging.");
}
}
```

- In the above code, modify the
`getRange("E1:E21")`

code element according to the cell range. If your data is under**column C**between**C2:C100**, enter`getRange("C2:C100")`

. - The current code will fetch averages for every three cells. If you need the mean values for a different combination, like every four cells, modify
`let N = 3`

to`let N = 4`

. - Click the
**Save script**button. - Hit the
**Run**button to execute the script. - The code will display the result under the
**Output**tab below the**Code Editor**.

## Conclusion

These are the easiest ways to calculate the average of every nth cell in Excel. You can use the **AVERAGE** function along with **OFFSET** and **ROW** to easily fetch the mean values of every nth cell. Alternatively, you can use the VBA scripts and Excel Automate.

Give the above methods a try and share your experience and review in the comment box below. If you also know a few ways to average every nth cell in Excel, add that in your comment.