Skip to main content

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.

Find minimum value excluding zero with formula

arrow blue right bubble Find minimum value excluding zero with formula

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.

Best Office Productivity Tools

Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolset:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 30-day free trial.

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (29)
Rated 5 out of 5 · 1 ratings
Denysptwn
Denysptwn
This comment was minimized by the moderator on the site
Saya mencari nilai alpa predikat dan tentunya nilai 0 sangat dibutuhkan, kenapa fungsi min tidak mendeteksi nilai 0 di excel saya?
Reply
0
0
crystal
crystal     Denysptwn
This comment was minimized by the moderator on the site
Hi Denysptwn,
The formula in this post helps to find the smallest value excluding zero.
If you want to include zero in the result, just use this formula: =MIN($A$1:$C$7)
Reply
Report
0
0
JavidP
JavidP
This comment was minimized by the moderator on the site
Hi Dear Srystal
Many thanks for you kindness and the time you spend for my problem and for solving it,
I didn't knew this way and this is very useful for future working.
As i have more than 700 rows in my sheet and needed to sort my data too and because of some merged cells it was very hard to do it, therefore i askd fir help to have some VBA code and some kind friends kindly sent me a code and soled my problem.
I am Sorry, I forgot to let you know that the problem has been resolved.
Thank you again and hope to be able to repay the love of all of you friends.
Good luck dear
Sincerely yours, Javid
Reply
Report
0
0
crystal
crystal     JavidP
This comment was minimized by the moderator on the site
Hi Javid,
You may receive an error message when you sort a range that contains merged cells in Excel. There is no directly way to handle this issue. You need to manually unmerge the merged cells first to sort all cells. You can following the methods in the following article. Hope I can help. Thank you for your comment.
How To Sort Data With Merged Cells In Excel?
Reply
Report
1
0
JavidP
JavidP     crystal
This comment was minimized by the moderator on the site
Hi dear Crystal,
Good to see your comment again :)
Many Thanks for your useful information and suggestion.
After facing many problems, I decided to change my strategy and the first step was to remove the merged cells and replace them with new cells and new numbers.
I'm making some changes, including the one I mentioned.
I hope I will face less problems from now on.
Thanks again with love,
Have a nice times.
Reply
Report
0
0
Rated 5 out of 5
JavidP
JavidP
This comment was minimized by the moderator on the site
Hi
Can anyone help me to use formula to find small value except zero and highlight it with conditional format?
Reply
Report
0
0
crystal
crystal     JavidP
This comment was minimized by the moderator on the site
Hi,
Please follow the steps below to get it done.
1. Click Home > Conditional Formatting > New Rule.
2. In the Edit Formatting Rule dialog box, select Format only cells that contain in the Select a Rule Type list box; Select equal to in the second drop-down list, and then enter the formula below (don't forget to change the cell references):
=SMALL($F$5:$O$21, COUNTIF($F$5:$O$21, "<=0")+1)
3. Click the Format button to specify a fill color to highlight the cells.
4. Click OK.
See the uploaded screenshot below.
View Attachments (1 / 5)
Reply
Report
1
0
Zingo
Zingo
This comment was minimized by the moderator on the site
Hi, how to search for the minimum of values present in multiple matrices, always excluding 0 if the matrices are on different sheets?
Reply
Report
0
0
crystal
crystal     Zingo
This comment was minimized by the moderator on the site
Hi Zingo,Sorry I can't help to solve this problem. Here suggest you post the problem to the forum below to get help from other Excel enthusiasts. https://www.extendoffice.com/forum/kutools-for-excel.html 
Reply
Report
1
0
Justin
Justin
This comment was minimized by the moderator on the site
I have a formula that looks the the lest number in a seperate workbook, but if the data is not fully entered or collected yet, the return value is 0, rather than the smallest number. is there a way to solve this via the formula or a feature in exceel?
=MIN(IF('Input Data 54 mm'!$D:$D='Index Values 54 mm'!$A7,('Input Data 54 mm'!J:J)*95%))
Reply
0
0
crystal
crystal     Justin
This comment was minimized by the moderator on the site
Hi,
Sorry I can't solve this problem. You can post the problem to the forum below to get help from other Excel enthusiasts.
https://www.extendoffice.com/forum/kutools-for-excel.html 
Reply
Report
0
0
Dop
Dop
This comment was minimized by the moderator on the site
How do I find the small/lowest number that is not 0 based on certain conditions, e.g.: find the minimum number in a row, based on the columns with "covid"?
I typed this =MIN(IF($C$8:$AF$8=$AK$3,C10:AF10<>0,C10:AF10)) but it doesn't evaluate this $C$8:$AF$8=$AK$3, it only returns C10:AF10<>0,C10:AF10. where AK3 = covid.
Reply
Report
0
0
Hafeez ur rehman
Hafeez ur rehman
This comment was minimized by the moderator on the site
what to do if you have zeros in a row. for example comparative statement with not quoted items?
Reply
0
0
francis
francis
This comment was minimized by the moderator on the site
update 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?
Reply
0
0
francis
francis
This comment was minimized by the moderator on the site
Crystal, you are amazing! thank you thank you so much for the help :-) made my life/work easier
Reply
0
0
francis
francis
This comment was minimized by the moderator on the site
This 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
Reply
0
0
crystal
crystal     francis
This comment was minimized by the moderator on the site
Hi francis,
Please apply this formula: =INDEX($A$1:$C$1,0,MATCH(MIN($A2:$C2),$A2:$C2,0)) as the below screenshot shown.
View Attachments (1 / 5)
Reply
Report
0
0
octavia
octavia     crystal
This comment was minimized by the moderator on the site
Hi Crystal, how to excludie zero value in this formula
Reply
0
0
David
David
This comment was minimized by the moderator on the site
Thank 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
Reply
0
0
crystal
crystal     David
This comment was minimized by the moderator on the site
Hi 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.
Reply
Report
0
0
drifter
drifter
This comment was minimized by the moderator on the site
The 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 B1=MIN(B1,A3)
cell C1=MIN(B1,A2)

Original results:
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 B1=MIN(B1,37)

New results:
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.

Final results:

cell A1: 37 cell A3: 26
cell B1: 26
cell C1: 26
Reply
0
0
Ify
Ify
This comment was minimized by the moderator on the site
Worked perfectly well, thank you!
Reply
0
0
ChandraShekhar
ChandraShekhar
This comment was minimized by the moderator on the site
Thank you for the "=SMALL(A1:E7,COUNTIF($A$1:$E$7,0)+1)" formula.
Reply
0
0
christopher
christopher
This comment was minimized by the moderator on the site
what 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)
Reply
0
0
TIKI
TIKI
This comment was minimized by the moderator on the site
NONE OF THEM WORKED KEPT GETTING 0
Reply
0
0
crystal
crystal     TIKI
This comment was minimized by the moderator on the site
Hi TIKI,
Which Excel version are you using?
Reply
Report
0
0
Brent Giles
Brent Giles
This comment was minimized by the moderator on the site
This works as long as all your numbers in your range are positive numbers.
Reply
0
0
carlo
carlo     Brent Giles
This comment was minimized by the moderator on the site
try this for an interesting "twist".... If a number is zero, use a simple formula IF(A1>0;A1,99999) to replace it with an impossibly high number, then when selecting the lowest it's not necessary to worry about counting zeros. I've found that counting zeros doesn't work well when there is more than one zero value in the array anyway. The ranking doesn't appear to rank all the rows with zero the same... it appears to incrementally add one to the rank for each zero.
Reply
0
0
Ben
Ben     carlo
This comment was minimized by the moderator on the site
this is brilliant and allows you to evaluate non-contiguous arrays - thanks! 
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations