Average Non-Adjacent Cells and Ranges in Excel
Question details
Calculate an average from selected non-adjacent cells or ranges without including the cells between them.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs an AVERAGE formula that uses several separate ranges in the same worksheet.
- Observed behavior
- A colon placed between multiple references can create one continuous range and include cells that were intended to be skipped.
Identify each individual cell or continuous range that should be included in the calculation. Check your regional formula settings to determine whether formulas use commas or semicolons as argument separators.
Separate non-adjacent Excel references with commas in AVERAGE
Use this method when the cells or ranges you want to average are separated by rows or columns that must not be included.
The AVERAGE function accepts multiple cell references and ranges. Separate each reference with a comma rather than linking all references with colons, because a colon defines a continuous range.
Click the blank cell where you want the average to appear.
Type =AVERAGE(E11:E12,E14:E16,E18:E20), replacing the example references with the cells or ranges you want to include.
Press Enter to calculate the average of only the listed ranges.
Click the formula cell and review the formula bar to confirm that each separate range is divided by a comma.
Average individual non-adjacent Excel cells
Use this method when you need to average specific separate cells rather than complete ranges.
Average Separate Cells and Ranges with WPS Spreadsheets
WPS Spreadsheets lets you calculate averages from non-adjacent cells and ranges with the familiar AVERAGE function. Enter each selected reference as a separate argument so skipped rows and columns are not included in the result.
- 1. Open your workbook in WPS Spreadsheets: Open the Excel workbook, then select the worksheet containing the values to average.
- 2. Choose an output cell: Click the blank cell where the calculated average should be displayed.
- 3. Enter separate range arguments: Type =AVERAGE(E11:E12,E14:E16,E18:E20) in the formula bar, changing the references as needed.
- 4. Calculate the result: Press Enter to average only the specified non-adjacent ranges.
Frequently Asked Questions
Why does my AVERAGE formula include cells I did not select?
A colon creates a continuous range. For example, references joined from E11 through E20 can include skipped cells such as E13 and E17. Separate distinct ranges with commas or semicolons instead.
Can I use AVERAGE with both individual cells and ranges?
Yes. You can combine them in one formula, such as =AVERAGE(E11,E14:E16,E20). Each cell or range must be a separate function argument.
Should I use commas or semicolons in an Excel AVERAGE formula?
Most English-language installations use commas. If your regional settings use semicolons as list separators, enter =AVERAGE(E11:E12;E14:E16;E18:E20) instead.
How do I exclude blank cells from an average in Excel?
The AVERAGE function ignores blank cells within the references you provide. To avoid including unwanted values, list only the intended non-adjacent cells or ranges in the formula.
