How to Average Values Greater Than Zero in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Average Values Greater Than Zero


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.

Using AVERAGEIF Function to Average Values Greater Than Zero in Excel

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

Applying AVERAGEIFS Function in Excel to Average Values Greater Than Zero

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,""))

Employing AVERAGE and IF Functions to Average Values Greater than Zero in Excel

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
  • Finally, press ENTER and you will get the Average Profit.

Read More: [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)


Similar Readings


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
  • Finally, press ENTER to get the Average Profit.

Use of SUMIF and COUNTIF Functions to Average Values Greater Than Zero in Excel

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.

How to Average Column Based on Criteria of Another Column in Excel

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.

Practice aheet for How to 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

Mashhura Jahan
Mashhura Jahan

Hey! Welcome to my profile. Currently, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo