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.

Question? Ask it in our Excel Forum


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

Similar Content on TeachExcel
Formula to Get Value of Last Non-Empty Cell in Excel
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range in Excel....
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...
ActiveCell vs Selection VBA for Excel
Tutorial: The difference between ActiveCell and Selection in Excel VBA and when to use them. Both pr...