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

ByTamal Das

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:

  1. Open your Excel worksheet and highlight the column or row containing the data you want to average.
  2. 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.
  3. Select an empty cell where you want to display the average result.
  4. In that empty cell, enter the following formula:
The AVERAGE formula to mean every nth row in Excel
The AVERAGE formula to mean every nth row in Excel
=AVERAGE(OFFSET($E$2,(ROW()-ROW($F$2))*4,,4,))
  1. 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.
  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.
  3. The current formula derives the average of every four cells under column E. Hence, you see *4,,4 in the formula.
  4. 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.
Average of first 4 cells in Excel
Average of first 4 cells in Excel
  1. Now, simply hit the Enter key to get the average of the first four cells under column E.
Learn how to average every nth row in Excel using AVERAGE, OFFSET, and ROW
Learn how to average every nth row in Excel using AVERAGE, OFFSET, and ROW
  1. Drag the fill handle until the end of the column (where data ends) to generate the mean values of every 4th cell automatically.
  2. 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:

Save as dialog box in Excel
Save as dialog box in Excel
  1. Click File and choose Save As on the left-side panel that appears.
  2. Then, choose Browse and select a folder like Downloads.
  3. The Save As dialog box will pop up.
  4. There, click the Save as type dropdown menu and choose the Excel Macro-Enabled Workbook option.
  5. 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:

The VBA Editor in Excel
The VBA Editor in Excel
  1. Hit Alt + F11 to bring up the VBA Editor tool.
  2. Click the Insert button on the VBA toolbar and select Module.
  3. A new blank Module will show up on the VBA Editor backstage (the grey board).
  4. 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
  1. 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.
  2. Click Save on the toolbar and close the tool.
Running a macro in Excel
  1. Hit Alt + F8 to bring up the Macro dialog box and select Average_Row_Range.
  2. Click Run to get average values.
  3. 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:

How to open New Script for Office Scripts
How to open New Script for Office Scripts
  1. Open the Excel file where you have got values for which you need averages after certain intervals.
  2. Click the Automate tab on the ribbon menu and then select the New Script button inside the Scripting Tools command block.
  1. You should now see the Code Editor on the right side of Excel.
  2. 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.");
  }
}
  1. 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").
  2. 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.
  3. Click the Save script button.
  4. Hit the Run button to execute the script.
  5. 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.

Similar Posts