Average Cells Excluding Zeros in Excel

Exclude zeros while averaging cells in Excel. This method removes all zeros from the equation.

Sections:

Easy Method

Method 2 (Used for Excel 2003 and Older)

Notes

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.