Average Cells Excluding Zeros in Excel

Author:

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.

Question? Ask it in our Excel Forum

Excel Function: AVERAGE(), AVERAGEIF(), IF()

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 applies th...
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 ...
Tutorial Details
Excel Function: AVERAGE(), AVERAGEIF(), IF()