# 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
`=AVERAGEIF(D5:D15, "<>0")`

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).

## 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
`=AVERAGE(IF(D5:D15<>0, D5:D15))`

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.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF