How to average non-adjacent/contiguous cells excluding zeros in Excel?
While working with Excel data, there are many occasions where you need to calculate the average of a set of numbers, but not all relevant cells are adjacent to each other, and you also wish to exclude zero values (which may represent missing data or zeroed-out entries). For instance, in a sales or inventory sheet, certain items may have zero stock or no sales on specific dates; in these cases, simply calculating the average might not give you a true sense of the data trend since zeros can skew the result. Excluding them helps to analyze active data points only.
Suppose you have a fruit table like the screenshot below, and you want to calculate the average of all cells in the Quantity column, excluding zeros. The following methods will guide you through multiple practical approaches to achieve this in Excel, allowing you flexibility whether your data is dispersed in non-adjacent cells, spread across multiple rows/columns, or when you want to utilize built-in Excel functionalities or simple formulas.
- Average non-adjacent cells excluding zeros with formula
- Average non-adjacent cells excluding zeros with a VBA macro
- Average non-adjacent cells excluding zeros using Excel built-in function
Average non-adjacent cells excluding zeros with formula
This method uses an array formula to quickly calculate the average of non-adjacent cells (for example, every other cell in a row or column), while excluding any zeros among the selected cells. This is applicable when your target cells follow a fixed interval pattern. It is especially efficient when dealing with periodic data, such as every other day's value, or data that is spaced at regular intervals but not directly next to each other.
Limitations: This approach is best used when non-adjacent cells are arranged with a consistent interval. For arbitrary, manual selections, see the formula-based method below.
1. Select a blank cell where you want the result to appear, enter the array formula:
=AVERAGE(IF(MOD(COLUMN(C2:G2)-COLUMN(C2),2)=0,IF(C2:G2,C2:G2)))Then press Ctrl + Shift + Enter keys together (for older Excel versions; in Excel 365 or 2019, pressing Enter is sufficient as they support dynamic arrays).
Note: In the formula, C2 and G2 represent the first and last cells in your target range. The number "2" sets the column interval; adjust this value if your non-adjacent cells are separated by a different number of columns. Modify the range (C2:G2) and the interval (2) according to your data layout.
Tips:
- Ensure your chosen range covers all the non-adjacent cells you intend to average.
- If your data selection is not based on regular intervals, use the next formula-based solution for arbitrary cells.
- This formula naturally ignores blank cells and zero values, giving you the true average of meaningful data points.
- To avoid errors, check that your cell references are correct and you pressed the required keyboard combination for array formulas in older Excel versions.
Average non-adjacent cells excluding zeros with a VBA macro
If you need to quickly average a group of non-adjacent cells while omitting zeros, and your selection doesn’t follow a fixed pattern (for example, you want to manually select a collection of specific cells across different rows and columns), a VBA macro offers a flexible and powerful solution. The code will loop through your selected cells and calculate the average, excluding any cells containing a zero.
This method is excellent for:
- Quickly processing large or irregular data selections.
- Saving your custom logic for repeated use on similar datasets.
- Bypassing the limitations of traditional worksheet formulas for complex selections.
1. Click Developer > Visual Basic; in the window that opens, click Insert > Module, and paste the following code into the module:
Sub AverageNonAdjacentExcludeZero()
Dim rng As Range
Dim cell As Range
Dim SumVal As Double
Dim CountVal As Long
Dim SelectedRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set SelectedRng = Application.Selection
Set SelectedRng = Application.InputBox("Select non-adjacent cells to average (exclude zeros)", xTitleId, Type:=8)
On Error Resume Next
SumVal = 0
CountVal = 0
For Each cell In SelectedRng
If IsNumeric(cell.Value) And cell.Value <> 0 Then
SumVal = SumVal + cell.Value
CountVal = CountVal + 1
End If
Next
If CountVal > 0 Then
MsgBox "Average (excluding zeros): " & SumVal / CountVal, vbInformation, xTitleId
Else
MsgBox "No non-zero numeric cells selected.", vbExclamation, xTitleId
End If
End Sub2. Click the 
Run button, or press F5 in the VBA editor. A dialog will appear asking you to select your cells. Use your mouse and Ctrl key to click and select non-adjacent cells you wish to average. Click OK and the macro will display the result, excluding any zeros.
Average non-adjacent cells excluding zeros using Excel built-in function
Excel also provides some built-in methods to handle this task with little to no formula work, suitable for quick, one-off calculations or visual inspections.
Use the Status Bar:
- Hold Ctrl and use your mouse to manually select each non-adjacent cell you want to average, skipping any cells with zero values.
- Once selected, look at the Excel status bar at the bottom right of your window—the Average value will be displayed automatically if more than one cell is selected.
- This method is fast for visual checks and small datasets, but does not output to a worksheet cell.
When choosing a suitable method, consider the size of your data, how scattered your selected cells are, and whether you need a repeatable formula output or just a quick visual check. If you encounter errors (such as #DIV/0!), ensure that at least one non-zero value exists in your selections. When using VBA, always save your workbook before running new macros to prevent accidental data loss.
Quickly select interval (non-adjacent) rows or columns in specified range in Excel:
The Kutools for Excel's Select Interval Rows & Columns feature can help you easily select interval rows or columns in specified range in Excel as the below screenshot shown. This can be handy before using formulas or macros by helping you select the exact cells you want to process.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in