2 Methods to Calculate the Average Excluding 0

Example Model:

Method 1 – Using the AVERAGEIF Function to Calculate the Average (Excluding 0)

Steps:

• Merge cells E5 to E15, then select the merged cells.

• Enter the following formula into the formula bar

Note: D5:D15 is the cell of the function, <>0 = criteria which means the cell’s value is greater than zero.

• Press Enter to get the average excluding 0

• Count the cells that contain zero value (the average becomes 66.27).

Read More: How to Average Values Greater Than Zero in Excel

Method 2 – Combining the AVERAGE and IF Functions to Calculate the Average Excluding 0

Steps:

• Select cell E5

• Enter the formula into the formula bar

Note: D5:D15<>0 = logical_test shows the cell that contains a value greater than zero also D5:D15 = value_if_true -the value of the cells.

• Press Enter to get the average excluding 0

Note: The average including 0 becomes 66.27.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

• How to Calculate VLOOKUP AVERAGE in Excel
• How to Find Average with OFFSET Function in Excel
• How to Use VBA Average Function in Excel
• How to Add Average Line to Excel Chart

<< Go Back to Conditional Average | Calculate Average | How to Calculate in Excel | Learn Excel