Zehad Rian Jim
This tutorial will demonstrate how to use the AVERAGEIF function greater than 0 in excel. The AVERAGEIF function returns the average of the cells of an array that satisfy one or more given criteria. The criteria can be of the same array or a different array. In every company, educational institution, or anywhere this function is very important as it gives the proper average results to compare for a certain condition. In our case, we will try to find out the average greater than 0 using the AVERAGEIF function.
Download Practice Workbook
You can download the practice workbook from here.
Step-by-Step Procedures to Use AVERAGEIF Function for Values Greater Than 0 in Excel
Now, we will try to find out the average greater than 0 using the AVERAGEIF function in 3 steps. If you follow the steps correctly, you should learn how to show the print area in Excel on your own. The steps are:
Step 1: Arranging a Dataset
Our goal is to arrange a dataset for ease of our understanding. In this case, we have the Product in column B, the Quantity in column C, and the Average Greater Than 0 in column D. We will gonna use this dataset to describe the whole process. The whole dataset image is below.
Step 2: Inserting Proper Formula
Now, we want to get the average of only the quantities that are above zero in number. To do that we will gonna use the AVERAGEIF function. Insert the following formula in the D5 cell.
=AVERAGE(IF(C5:C11<>0, C5:C11))
Step 3: Showing Final Result
Lastly, after using the formula, press the Enter button to get the desired result. You can easily change the formula according to your need.
Read More: Excel AVERAGEIF with ‘Greater Than’ and ‘Less Than’ Criteria
How to Use AVERAGEIF Function Between Two Values in Excel
We want to use the AVERAGEIF function to find the desired result between two values in excel. It is very important and useful for determining the final result while two-valued conditions are available. We can fulfill our goal by following the steps.
Steps:
- At first, arrange a dataset like the following image.
- Second, in the E5 cell insert the following formula.
=AVERAGEIFS(D5:D11,C5:C11,">=75",C5:C11,"<=85")
- Last, press the Enter button to get the desired result.
Read More: How to Calculate Average If Number Matches Criteria in Excel
How to Use AVERAGEIF Function for Cell Containing Text in Excel
Next, we want to use the AVERAGEIF function to find the desired result while the cell contains the text. In day-to-day life, it is the most used application for this function. Every store or company needs to find out the average of their products that contain text. So, it is very important to learn this process. We can do that by following the below steps.
Steps:
- To begin with, arrange a dataset like the following image.
- In addition, in the D5 cell insert the following formula.
=AVERAGEIF(B5:B11,"*Desktop*",C5:C11)
- Finally, press the Enter button to get the desired result.
How to Use AVERAGEIF Function Between Two Dates in Excel
Moreover, we want to use the AVERAGEIF function to find the desired result between two dates in excel. When we are dealing with any project or trying to deliver a certain product to customers or achieve a certain goal, we always need to compare certain values between date ranges. So, we need to learn this process by following the below steps.
Steps:
- Firstly, arrange a dataset like the following image.
- Secondly, in the E5 cell insert the following formula.
=AVERAGEIF(C5:C11,">1/10/2022",D5:D11)
- Lastly, press the Enter button to get the desired result.
Things to Remember
- In the case of using the formulas the most important thing is to select the desired cells properly. If you don’t select the cells properly then it can get you a confusing result.
- When you are dealing with this function with texts make sure that you have spelled the text exactly like the dataset. If you don’t spell it exactly, excel won’t able to find any result.
- You should download the excel file and use it for better understanding.
Conclusion
Henceforth, follow the above-described methods. Hopefully, these methods will help you to use an AVERAGEIF greater than 0 in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our level best to solve the problem or work with your suggestions.
