Mashhura Jahan
If you have a dataset that contains both negative and non-negative values and want to calculate the average of values that are greater than zero then Excel can be helpful for you. In this article, you will get to know how you can average values greater than zero in Excel.
Download Practice Workbook
4 Easy Ways to Average Values Greater Than Zero in Excel
Here, I have taken the following dataset that contains the Month and the Profit columns. The Profit column contains both positive and negative values. Here, the negative value means loss, and 0 means a breakeven point. I will show you how you can average values greater than zero in Excel by using this dataset. I will explain 4 easy and effective ways.
1. Using AVERAGEIF Function to Average Values Greater Than Zero in Excel
In this first method, I will explain how you can average values greater than zero by using the AVERAGEIF function. Suppose you have the following dataset that contains both positive and negative Profits. Here, negative profits mean loss. And, you want to calculate the Average Profit which means the average of the values that are greater than zero.
Let me show you how you can do it.
Steps:
- Firstly, select the cell where you want to calculate the Average Profit. Here, I selected cell C12.
- Secondly, in cell C12 write the following formula.
=AVERAGEIF(C5:C10,">0")
Here, in the AVERAGEIF function, I selected C5:C10 as the range and “>0” as the criteria. The formula will return the average of the values from the range that match the criteria.
- Thirdly, press ENTER and you will get your Average Profit.
Read More: How to Average Negative and Positive Numbers in Excel
2. Applying AVERAGEIFS Function in Excel
Here, I will explain how you can average values greater than zero by applying the AVERAGEIFS function. Let’s see the steps.
Steps:
- Firstly select the cell where you want to average values greater than zero. Here, I selected cell C12.
- Secondly, in cell C12 write the following formula.
=AVERAGEIFS(C5:C10,C5:C10,">0")
Here, in the AVERAGEIFS function, I selected cell range C5:C10 as average_range. Then, I selected cell range C5:C10 as criteria_range1 and “>0” as criteria1. Now, the formula will return the average of the values from the average_range that match the criteria1.
- Finally, press ENTER to get the result.
Read More: How to Calculate Average True Range in Excel (with Easy Steps)
3. Employing AVERAGE and IF Functions
In this method, I will show you how you can average values greater than zero by employing the AVERAGE function and the IF Function. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to calculate the Average Profit.
- Secondly, in that selected cell write the following formula.
=AVERAGE(IF(C5:C10>0,C5:C10,""))
Formula Breakdown
- IF(C5:C10>0,C5:C10,””) —-> Here, the IF function will check if C5:C10>0. If the logical_test is True then the formula will return C5:C10. Otherwise, it will return a blank.
- Output: {1000;500;””;””;700;””}
- AVERAGE(IF(C5:C10>0,C5:C10,””)) —-> turns into
- AVERAGE({1000;500;””;””;700;””}) —-> Now, the AVERAGE function will return the average of the values.
- Output: 733.33333
- AVERAGE({1000;500;””;””;700;””}) —-> Now, the AVERAGE function will return the average of the values.
- Finally, press ENTER and you will get the Average Profit.
Read More: [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
Similar Readings
- How to Ignore #N/A Error When Getting Average in Excel
- Calculate Average Numbers in Excel (9 Handy Methods)
- How to Average Filtered Data in Excel (2 Easy Methods)
- Fix Divide by Zero Error for Average Calculation in Excel
- How to Calculate Average from Different Sheets in Excel
4. Use of SUMIF and COUNTIF Functions to Average Values Greater Than Zero
In this method, I will explain how you can use the SUMIF and COUNTIF functions to average values greater than zero in Excel. Let’s see the steps.
Steps:
- Firstly, select the cell where you want to calculate the Average Profit. Here, I selected cell C12.
- Secondly, in cell C12 write the following formula.
=SUMIF(C5:C10,">0",C5:C10)/COUNTIF(C5:C10,">0")
Formula Breakdown
- SUMIF(C5:C10,”>0″,C5:C10) —-> Here, the SUMIF function will return the summation of the values that match the criteria.
- Output: 2200
- COUNTIF(C5:C10,”>0″) —-> Here, the COUNTIF function will count the number of cells that match the criteria.
- Output: 3
- SUMIF(C5:C10,”>0″,C5:C10)/COUNTIF(C5:C10,”>0″) —-> turns into
- 2200/3 —-> Now, the formula will divide 2200 by 3.
- Output: 733.33333
- 2200/3 —-> Now, the formula will divide 2200 by 3.
- Finally, press ENTER to get the Average Profit.
Read More: How to Calculate Sum & Average with Excel Formula
How to Average a Column Based on Criteria of Another Column in Excel
In this section, I will show you how to average a column based on the criteria of another column in Excel. Here, I have taken the following dataset to explain this example. This dataset contains the Month, Sales, and Profit columns. I will show you how you can calculate the Average Sales if the Profit is greater than zero.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want to calculate the Average Sales. Here, I selected cell C15.
- Secondly, in cell C15 write the following formula.
=AVERAGEIF(D5:D10,">0",C5:C10)
Here, in the AVERAGEIF function, I selected cell range D5:D10 as the range and “>0” as the criteria. Then, I selected the cell range C5:C10 as average_range. Now, the formula will return the average of the values from average_range that match the criteria.
- After that, press ENTER to get the Average Sales.
Read More: How to Calculate Monthly Average from Daily Data in Excel
Things to Remember
- You should keep in mind that If the AVERAGEIF function fails to meet the criteria then it will return #DIV/0! error.
Practice Section
Here, I have provided a practice sheet for you to practice how you can get average values greater than zero in Excel.
Conclusion
To conclude, I tried to cover how you can average values greater than zero in Excel. Here, I explained 4 easy ways of doing it. I hope this article was helpful for you. Lastly, if you have any questions feel free to let me know in the comment section below.
Related Articles
- How to Find Average of Specific Cells in Excel (3 Handy Ways)
- Calculate Average of Averages in Excel (with Easy Steps)
- How to Calculate Average Only for Cells with Values in Excel
- Calculate Average of Multiple Ranges in Excel (3 Methods)
- How to Calculate Average of Multiple Columns in Excel (6 Methods)
