We are in the process of updating the site, so some pages still have the older design.
Average Non-Contiguous Cells in Excel
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.
Download the Project Files
Follow along with the tutorial by downloading the files used in it. (Completely Free)
Phone number is optional - we use it to send course discounts and weekly updates via SMS.
View our Privacy Policy | Terms of Use
Question? Ask it in our Excel Forum
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.)
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 ...
Tutorial: In this tutorial I am going to introduce the idea of Named Cells. A Named Cell is a cell w...
Macro: This macro will highlight all cells in excel which are not empty. This means that if a cel...
Tutorial: How to prevent a user from entering any spaces within text in Excel. This allows you to...
Tutorial: The difference between ActiveCell and Selection in Excel VBA and when to use them. Both pr...
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
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.)