How to find minimum value in a range excluding zero value in Excel?
Normally, zero value is supposed to be the minimum value among positive numbers. But in some cases, you need to find the minimum value in a range excluding the zero value. This article will show you method of finding minimum value excluding zero in Excel.
Supposing your number values locate in range A1:E7 as below screenshot shown. Please apply the following formula to get the minimum value in that range excluding zero in Excel.
1. Select a blank cell (H1) for placing the minimum value, enter formula =SMALL(A1:E7,COUNTIF($A$1:$E$7,0)+1) into the Formula Bar, and then press the Enter key.
Then the minimum value of specified range excluding zero is populated in the selected cell as above screenshot shown.
Note: You can also use the array formula =MIN(IF($A$1:$E$7 <>0,$A$1:$E$7)) + Ctrl + Shift + Enter keys to get the minimum value of a range excluding zero in Excel.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 months agoupdate on the formula from Crystal: the formula works but the it counts the blank cells as the lowest in the range. how do we fix the formula not to count for zero value or empty cells?
- To post as a guest, your comment is unpublished.· 3 months agoCrystal, you are amazing! thank you thank you so much for the help :-) made my life/work easier
- To post as a guest, your comment is unpublished.· 3 months agoThis was very useful. how do we revise the formula that instead of giving me the lowest value, it would identify under which heading is the lowest value on. Scenario is i have 3 suppliers, providing 3 different prices. i would like the formula to tell me which supplier has the lowest price (i.e. supplier 1, supplier 2, etc) and gives me that supplier's name so i could sort it later. Thank you
- To post as a guest, your comment is unpublished.· 3 months agoHi francis,
Please apply this formula: =INDEX($A$1:$C$1,0,MATCH(MIN($A2:$C2),$A2:$C2,0)) as the below screenshot shown.
- To post as a guest, your comment is unpublished.· 3 months agoThank you for this.
However can you help me with why this works?
You are essentially saying that you are counting values that are 0 in that range and adding 1 to it?
I do not understand, please explain.
Thanks in advance
- To post as a guest, your comment is unpublished.· 3 months agoHi David,
For the formula =SMALL(A1:E7,COUNTIF($A$1:$E$7,0)+1):
1. COUNTIF($A$1:$E$7,0): the COUNTIF function counts the number of cells with zero in cells A1 through E7, and returns the result as 1. Here means that there is only one zero cell in the range.
2. SMALL(A1:E7,1+1): Here we need to find the minimum value that is greater than zero. As zero is the first smallest value in range A1:E7, we need to return the 2nd smallest one, SMALL(A1:E7,2) means that to return the 2nd smallest number in range A1:E7.
- To post as a guest, your comment is unpublished.· 5 months agoThe issue is that the formula min() tell excell to compare the items in the brackets. If you tell excel to look at the compare the cell with the formula in it, it's value is zero.
Ex: you want cell B1 to report the lowest value ever reported for cell A3. So in cell B1 you enter: =min(B1,A1). (Circular reference error if you have not turned on iterations in options>formulas. Change iterations to more than 1) Excel returns 0.
Workaround: use the following formula:
cell A1=MAX(B1,A1) cell A3(data being analyzed) 37
cell A1: 37 cell A3: 37
cell B1: 0
cell C1: 0
(Will only do this part once) Set the original minimum value by changing the formula in B1. Replace A3 with 37:
cell A1: 37 cell A3: 37
cell B1: 37
cell C1: 37
Now change the formula in B1 back to what it was: cell B1=MIN(B1,A3). That is it. Now when you change the value in cell A3 the cell A1 will report the max and cell B1 will report the min. You obviously can hide the row with cell C1.
cell A1: 37 cell A3: 26
cell B1: 26
cell C1: 26
- To post as a guest, your comment is unpublished.· 7 months agoWorked perfectly well, thank you!
- To post as a guest, your comment is unpublished.· 8 months agoThank you for the "=SMALL(A1:E7,COUNTIF($A$1:$E$7,0)+1)" formula.
- To post as a guest, your comment is unpublished.· 8 months agowhat if my array of numbers are not contiguous? (ie $C$5:$I$6,$C$8:$I$8,$C$10:$I$12,$C$14:$I$14,$C$16:$I$28,$C$30:$I$31,$C$36:$I$37,$K$5:$P$6,$K$8:$P$8,$K$10:$P$12,$K$14:$P$14,$K$16:$P$28,$K$30:$P$31,$K$36:$P$37)
- To post as a guest, your comment is unpublished.· 1 years agoNONE OF THEM WORKED KEPT GETTING 0
- To post as a guest, your comment is unpublished.· 9 months agoHi TIKI,
Which Excel version are you using?
- To post as a guest, your comment is unpublished.· 3 years agoThis works as long as all your numbers in your range are positive numbers.