Average Non-Contiguous Cells in Excel

Add to Favorites

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

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...
Complex Structured References (Table Formulas) in Excel
Tutorial: How to use complex structured references, table formulas, in Excel. If you don't already...
Generate a Non-Repeating List of Random Numbers in Excel - UDF
Macro: Generate a series of non-repeating random numbers in Excel with this UDF (user defined fun...
Tutorial Details
Excel Function: FREQUENCY(), INDEX(), SUM()
Downloadable Files: Excel File
Similar Content
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...
Excel Forum