If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Minimum nonzero value
I need to find the minimum nonzero value in a row of numbers  not
necessarily together ie: 36, 0, 0, 24 i would like the resultant value to be 24 if there is a blank  36, 0, 0,  i would like it to be 36 any help? 
Ads 
#2




Minimum nonzero value
You can use the following array formula. It assume that your values
are in the range A1:A5. Change this to your actual range. =MIN(IF(A1:A5>0,A1:A5,MAX(0,MAX(A1:A5)))) Since this is an Array Formula, you *must* press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the Formula Bar enclosed in curly braces { }. (You do not type the curly braces  Excel includes them automatically.) The formula will not work properly if you do not use CTRL SHIFT ENTER. See http://www.cpearson.com/excel/ArrayFormulas.aspx for lots more information about array formulas. If the values in A1:A5 are any of (1) all empty, (2) all nonnumeric, or (3) all negative, the formula returns 0. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998  2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Mon, 6 Jul 2009 08:46:01 0700, Derrick > wrote: >I need to find the minimum nonzero value in a row of numbers  not >necessarily together >ie: >36, 0, 0, 24 >i would like the resultant value to be 24 >if there is a blank  >36, 0, 0,  >i would like it to be 36 > >any help? 
#3




Minimum nonzero value
Are there any negative numbers in the range?
Try one of these: =SMALL(A1:J1,COUNTIF(A1:J1,0)+1) This one is an array formula** : =MIN(IF(A1:J1>0,A1:J1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.  Biff Microsoft Excel MVP "Derrick" > wrote in message ... >I need to find the minimum nonzero value in a row of numbers  not > necessarily together > ie: > 36, 0, 0, 24 > i would like the resultant value to be 24 > if there is a blank  > 36, 0, 0,  > i would like it to be 36 > > any help? > 
#4




Minimum nonzero value
This is an ARRAY formula that must be entered using ctrl+shift+enter
=MIN(IF(H2:K2>0,H2:K2))  Don Guillett Microsoft MVP Excel SalesAid Software "Derrick" > wrote in message ... >I need to find the minimum nonzero value in a row of numbers  not > necessarily together > ie: > 36, 0, 0, 24 > i would like the resultant value to be 24 > if there is a blank  > 36, 0, 0,  > i would like it to be 36 > > any help? > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Minimum value look up  [email protected]  Excel Worksheet Functions  3  October 7th 08 02:35 AM 
minimum and the corresponding value  dinesh kumar  Excel Worksheet Functions  4  September 12th 07 08:59 PM 
How to lookup the minimum, 2nd minimum and 3rd minimum.........  Mark McDonough  Excel Worksheet Functions  8  July 15th 06 09:39 PM 
Minimum but One??  twogoodtwo  Excel Worksheet Functions  3  November 29th 05 03:52 PM 
Minimum Value  ceemo  Excel Worksheet Functions  2  August 9th 05 06:34 PM 