How to Calculate Average and Ignore Zero and Blank Cells in Excel

To calculate the average in Excel while ignoring zero and blank cells, you can use the AVERAGEIF function. AVERAGEIF allows you to specify criteria that must be met for a cell to be included in the average calculation.

Here's how to use the AVERAGEIF function to ignore zero and blank cells:

1. Open your Excel spreadsheet and locate the range of cells containing the data you want to average.
2. In an empty cell, type the following formula:

   =AVERAGEIF(range,">0")

   Replace range with the actual range of cells containing the data. For example, if your data is in cells A1:A10, the formula would look like this:

   =AVERAGEIF(A1:A10,">0")

3. Press Enter to calculate the average, ignoring zero and blank cells.

Example

Let's assume we have the following data in cells A1:A10:

10
20
0
30
(empty cell)
40
0
50
(empty cell)
60

To calculate the average ignoring zero and blank cells, we would use the following formula:

=AVERAGEIF(A1:A10,">0")

After entering the formula and pressing Enter, Excel will display the result: 35. This is the correct average of the non-zero, non-blank cells in the range (10, 20, 30, 40, 50, 60).