Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Average Cells Excluding Zeros in Excel
Exclude zeros while averaging cells in Excel. This method removes all zeros from the equation.
Sections:
Method 2 (Used for Excel 2003 and Older)
Easy Method
This method works for Excel 2007 and later versions, which probably includes you.
=AVERAGEIF(A1:A5,"<>0")
A1:A5 change this to the range that you want to average and that's it.
"<>0" is the part that tells the function to ignore cells that have zeros in them.
Result:
Method 2 (Used for Excel 2003 and Older)
If you have Excel 2003 or earlier, you must use this version of the formula.
=AVERAGE(IF(A1:A5<>0,A1:A5))
Array Formula - this is an array formula so you must enter it using Ctrl + Shift + Enter.
A1:A5 is the range that you want to average; make sure to change it in both parts of the formula to work with your data.
Result:
If you don't enter this formula correctly, you will see 2.4 as a result.
Notes
Excel 2007 introduced a lot of helpful functions, including the AVERAGEIF() function. Things like this will make your life much easier so, if you can, upgrade to at least Excel 2007, or the latest version. The ribbon menu will be a pain to learn but functions like this make it worth it.
Make sure to download the sample file for this tutorial to work with this example in Excel.
Question? Ask it in our Excel Forum
Macro: This free Excel macro formats a selection of cells as Text in Excel. This macro applies th...
Macro: This free Excel macro formats a selection of cells in the Time format in Excel. This Time ...
Tutorial: In this tutorial I will cover how to use the various formatting tools in Excel. The Format...
Tutorial: How to allow only certain people to edit certain cells or ranges in Excel. This is a sec...
Tutorial: How to remove all formatting from cells at once in Excel. This includes removing any and...
Tutorial: Average the results from a filtered list in Excel. This method averages only the visible ...