How to Find Minimum Value That Is Greater Than 0 in Excel

Get FREE Advanced Excel Exercises with Solutions!

If you are hoping to find the minimum value greater than zero in your large Excel dataset, you have come to the right place. As business analysts, we use Excel functions to look for specific values for our analysis. In this article, we will start from scratch and learn 5 easy ways to find the minimum value that is greater than 0 in Excel.

Overview of excel find minimum value greater than 0

Let’s take a look at the overview of this article which represents the finding of the minimum value greater than 0. You will learn about the methods in the next section of the article.


Download Practice Workbook

Download this workbook and practice while going through the article.


5 Suitable Ways to Find Minimum Value That Is Greater than 0 in Excel

Finding the smallest value other than zero can be cumbersome if we consider a large dataset. However, we can use the LARGE, COUNTIF, SMALL, COUNTIFS, AGGREGATE functions, Array formula, and Excel VBA to easily determine the values. Therefore, let’s jump into the methods.


1. Utilize Excel Array Formula to Get Minimum Value That Greater Than Zero

The first step aims to use the array formula to get the smallest positive integers in our dataset. Follow the below steps to do so.

Steps:

  • Initially, type the following array formula in cell D11,
=MIN(IF(C5:C9>0,C5:C9))
  • Afterward, press Ctrl + Shift + Enter keys since this is an array formula.

Utilizing Array Formula to find minimum value greater than 0 in Excel

  • Thus, we obtain the smallest sale $8.00 excluding $0.00.
Formula Breakdown
  • IF(C5:C9>0, C5:C9) specifies the logical condition in range C5:C9 where the value is greater than 0.
  • MIN(IF(C5:C9>0, C5:C9)) returns the minimum value greater than 0.

Read More: Min Max Inventory Calculation in Excel (with Easy Steps)


2. Combine LARGE and COUNTIF Functions to Find Minimum Value That Is Larger Than 0 in Excel

The objective of the second step is to combine the following functions. Firstly, the LARGE function returns the K-th largest value in a dataset where K is a positive integer. Secondly, the COUNTIF function returns the total number of occurrences of a specific value. Now, see the below procedure to join them.

Steps:

  • Firstly, write the combined formula in cell D11.
=LARGE(C5:C9,COUNTIF(C5:C9,">0"))
  • Later, press the Enter or Tab keys to see the output as $8.00.

Combining LARGE & COUNTIF functions to find minimum value greater than 0 in Excel

  • Hence, we obtained our desired result.
Formula Breakdown
  • COUNTIF(C5:C9,”>0″) counts the occurrences of the value that is greater than 0 in column C.
  • LARGE(C5:C9, COUNTIF(C5:C9,”>0″)) returns the minimum number greater than zero.

Read More: How to Use MIN Function to Exclude Zero in Excel (5 Easy Ways)


3. Merge SMALL and COUNTIFS Functions to Display Smallest Value Greater Than 0

In our third step, we will combine the SMALL and COUNTIFS functions before implementing the formula. The SMALL function returns the n-th smallest value in a dataset. Again, the COUNTIFS function returns the total occurrences of a given value in multiple criteria. Follow the below steps to combine them.

Steps:

  • First, in cell D11, type the combined formula.
=SMALL(C5:C9,COUNTIFS(C5:C9,0)+1)
  • Next, press the Enter key.

Merging SMALL & COUNTIFS functions to find minimum value greater than 0 in Excel

  • As a result, the smallest value other than zero, $8.00 pops up.
Formula Breakdown
  • COUNTIFS(C5:C9,0) counts the total 0 in the range C5:C9.
  • SMALL(C5:C9, COUNTIFS(C5:C9,0)+1) specifies the greater values than zero and returns 1 output.

Read More: How to Find Minimum Value with VLOOKUP in Excel (6 Ways)


4. Use AGGREGATE Function to Look for Minimum Value That Is Larger than Zero in Excel

The AGGREGATE function includes different functions like AVERAGE, COUNT, MAX, MIN, SUM, PRODUCT, LARGE, etc. with the Option to omit hidden rows and error values to get certain results. In this case, we will use the AGGREGATE function number 14 ( LARGE function) and Option 6 which represents omitting error values. See the steps below.

Steps:

  • To begin with, add the formula in cell D11:
=1/AGGREGATE(14,6,1/C5:C9,1)
  • Here, AGGREGATE (14, 6) represents the LARGE function with the Omit Error Value Option.
  • Pressing Enter will thoroughly get you the output.

Using AGGREGATE function to find minimum value greater than 0 in Excel

Read More: How to Calculate Average, Minimum And Maximum in Excel


5. Run a VBA Code in Excel to Determine Minimum Value That Is Greater than 0

In our last method, we will use a VBA code to find the smallest sale greater than 0. Carefully follow the given steps.

Steps:

  • Firstly, go to the Developer tab and click Visual Basic.

Opening the Visual Basic window

  • Subsequently, the Visual Basic window pops up.
  • There, tap the Insert tab and then Module to create a module box.

Creating a module box

  • In the Module box, type the following VBA code:
Sub SOFTekoMinVALUE()
ActiveCell.FormulaR1C1 = _
"=SMALL(R[-6]C[-1]:R[-2]C[-1],COUNTIFS(R[-6]C[-1]:R[-2]C[-1],0)+1)"
MsgBox "Minimum Value Greater than Zero is " _
& Worksheets("Excel VBA").Range("D11")
End Sub
			
  • Lastly, hit the green Run icon.

VBA code

  • Consequently, the desired output appears in a message box.

The output of finding a minimum value greater than 0 in Excel

Read More: How to Set a Minimum and Maximum Value in Excel (4 Easy Ways)


Conclusion

In conclusion, we have discussed some easy steps to find the minimum value that is greater than 0 in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please, leave any further queries or recommendations in the comment box below.


Related Articles

Yousuf Khan
Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo