Average Non-Contiguous Cells in Excel

Add to Favorites
Author:

How to average non-contiguous cells (cells that aren't next to each other) if those cells contain numbers and are greater than 0.

The regular AVERAGEIF() function will not work in this case; try it if you don't believe me!

Average Non-Contiguous Cells that are Greater than 0

This assumes that you have some cells around your spreadsheet that are not together in a single range.

So, let's say that we want to average cells A1,A5, and B2 so long as each cell contains a number and that number is greater than 0.

Use this formula:

=SUM(A1,A5,B2)/INDEX(FREQUENCY((A1,A5,B2),0),2)

To make this work for you, just replace the cell references A1,A5,B2 with the cells that you want to average.

There is really no point in explaining this function; it just works.

Here is the function in Excel:

Notes

Doing this relatively simple task requires an annoying formula in Excel. Currently, there isn't much you can do about this, so just use the formula above and you will be good to go.

Download the spreadsheet attached to this tutorial to see this example in Excel.


Excel Function: FREQUENCY(), INDEX(), SUM()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Average Cells Excluding Zeros in Excel
Tutorial: Exclude zeros while averaging cells in Excel. This method removes all zeros from the equa...
Linking Cells in Excel
Tutorial: In this tutorial I am going to cover how to link cells together. This is a useful feature ...
Naming Cells in Excel to Make Using Formulas/Functions Easier
Tutorial: In this tutorial I am going to introduce the idea of Named Cells. A Named Cell is a cell w...
Highlight Cells with Text or Formulas (non-empty cells)
Macro: This macro will highlight all cells in excel which are not empty. This means that if a cel...
Prevent Spaces from Being Entered in Cells in Excel
Tutorial: How to prevent a user from entering any spaces within text in Excel.  This allows you to...
ActiveCell vs Selection VBA for Excel
Tutorial: The difference between ActiveCell and Selection in Excel VBA and when to use them. Both pr...
Tutorial Details
Excel Function: FREQUENCY(), INDEX(), SUM()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course