Average Cells Excluding Zeros in Excel

Add to Favorites

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.

163eeb798b263bec4c8fc1461c6c3b7f.png

Result:

1a8b586f5a396824a9544467b5522b03.png

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.

d862dfef3745acbec26eab8cc791c5ff.png

Result:

f04bfdee347429c441dfdf9c8cda5197.png

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.


Excel Function: AVERAGE(), AVERAGEIF(), IF()
Downloadable Files: Excel File

Similar Content on TeachExcel
Format Cells as Text in Excel
Macro: This free Excel macro formats a selection of cells as Text in Excel. This macro appli...
Format Cells as Time in Excel
Macro: This free Excel macro formats a selection of cells in the Time format in Excel. This Time ...
How to Add Formatting to Cells and Data in Excel Styles, Fonts, Colors, & More
Tutorial: In this tutorial I will cover how to use the various formatting tools in Excel. The Format...
Allow Only Certain People to Edit Specific Cells in Excel
Tutorial: How to allow only certain people to edit certain cells or ranges in Excel. This is a sec...
Quickly Clear all Formatting in Excel
Tutorial: How to remove all formatting from cells at once in Excel. This includes removing any and...
Average the Visible Rows in a Filtered List in Excel
Tutorial: Average the results from a filtered list in Excel. This method averages only the visible ...