Md. Abdur Rahim Rasel
When we want to calculate an average in Microsoft Excel without a cell that contains zero, we can apply the AVERAGEIF, AVERAGE, and IF functions. Our 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 how to calculate an average in Excel excluding 0 by using the AVERAGEIF, AVERAGE, and IF functions.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Suitable Ways to Calculate Average in Excel Excluding 0
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 an average of the 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. Apply the 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. Please, follow the instructions below.
Steps:
- First of all, merge cells E5 to E15. Then select the merged cells.
- Further, type the AVERAGEIF function in the Formula Bar. The AVERAGEIF function in the Formula Bar is,
=AVERAGEIF(D5:D15, "<>0")- Where D5:D15 is the cell range 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 Calculate Average in Excel (Including All Criteria)
Similar Readings
- [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
- How to Get Average Time in Excel (3 Examples)
- Generate Moving Average in Excel Chart (4 Methods)
- How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)
- Calculate 5 Star Rating Average in Excel (3 Easy Ways)
2. Insert AVERAGE and IF Functions to Calculate Average in Excel Excluding 0
In this method, we’ll calculate an average of the ordered products in different months excluding zero order in some months in Excel by applying the AVERAGE and IF functions. These functions can be applicable when cells are blank or contain text also. Let’s follow the steps below to learn!
Steps:
- Select cell E5 first to calculate an average excluding 0.
- Hence, write down the AVERAGE and IF functions in the Formula Bar. The functions are,
=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.
Read More: How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)
Things to Remember
👉 The AVERAGEIF Function return #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”)Conclusion
I hope all of the suitable methods mentioned above to calculate an average excluding zero will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
Related Articles
- Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
- How to Calculate Average and Standard Deviation in Excel
- Calculate Class Average in Excel (6 Easiest Methods)
- Moving Average Formula in Excel (8 Uses with Examples)
- How to Calculate Average Percentage Change in Excel (3 Simple Ways)
