When we want to calculate an average in Microsoft Excel without a cell that contains zero, we can apply the combination of AVERAGEIF, AVERAGE, and IF functions. Today’s dataset is about different types of products that have been ordered in different months. In this article, we’ll learn two quick and suitable ways to calculate an average in Excel excluding 0 by using the AVERAGEIF, AVERAGE, and IF functions.
Let’s say, we have a dataset that contains information about different types of Products and their Quantity that has been ordered in different Months are given in columns C, D, and B respectively. We’ll calculate the average quantity of those ordered products excluding zero orders in several months in Excel. Here’s an overview of the dataset for today’s task.
1. Applying AVERAGEIF Function to Calculate Average in Excel Excluding 0
We can easily calculate an average excluding 0 in Excel by applying the AVERAGEIF function. This is the easiest and most time-saving function to calculate an average excluding 0 in Excel.
Steps:
- First of all, merge cells E5 to E15. Then select the merged cells.
- Further, type the AVERAGEIF function in the formula bar. The formula based on the AVERAGEIF function is:
=AVERAGEIF(D5:D15, "<>0")- Where D5:D15 is the cell of the function.
- <>0 = criteria which means the cell’s value is greater than zero.
- Hence, simply press Enter on your keyboard, and you will get an average excluding 0 as 81 which is the return of the AVERAGEIF function that has been given below screenshot.
- After that, we count the cells that contain zero value, the average becomes 66.27. From the below screenshot, you will be able to understand the difference between the average of including and excluding zero.
Read More: How to Average Values Greater Than Zero in Excel
2. Combining Excel AVERAGE and IF Functions to Calculate Average Excluding 0
In this method, we’ll calculate an average of the ordered products in different months excluding zero orders in some months in Excel by applying the AVERAGE and IF functions. These functions can be applicable when cells are blank or contain text.
Steps:
- Select cell E5 first to calculate an average excluding 0.
- Hence, write down the AVERAGE and IF functions in the formula bar. The formula will be:
=AVERAGE(IF(D5:D15<>0, D5:D15))- Where D5:D15<>0 = logical_test which means a cell that contains a value greater than zero.
- D5:D15 = value_if_true which means the value of the cells.
- After that, press Enter on your keyboard, and you will get an average excluding 0 as 81 which is the return of the AVERAGE and IF functions that have been given below screenshot.
- Further, we’ll calculate the average value of the cells including zero value, and the average including 0 becomes 27. From the below screenshot, you will be able to understand the average including and excluding zero.
Things to Remember
👉 The AVERAGEIF function returns #DIV/0! error when the value of all cells became non-numeric.
👉 If you’ve been using Excel 2003, you can apply a formula like this:
=SUM(range) / COUNTIF(range, “<>0”)Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to calculate an average excluding zero will provoke you to apply them in your Excel spreadsheets more efficiently. You are most welcome to feel free to comment if you have any questions or queries.
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
- Calculate the Average of an Array with VBA
- How to Add Average Line to Excel Chart
<< Go Back to Conditional Average | Calculate Average | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
