# Average Non-Contiguous Cells in Excel

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.

Excel Function: FREQUENCY(), INDEX(), SUM()

#### Question? Ask it in our Excel Forum

Our Excel Courses

#### Professional Input Forms in Excel • Skill Level: All Levels
• |
• 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

#### Send Emails from Excel (Multiple Methods) • Skill Level: All Levels
• |
• 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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...
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()