Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Minimum non-zero value
I need to find the minimum non-zero 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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Minimum non-zero 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:A50,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 non-numeric, 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 non-zero 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Minimum non-zero 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:J10,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 non-zero 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Minimum non-zero value
This is an ARRAY formula that must be entered using ctrl+shift+enter
=MIN(IF(H2:K20,H2:K2)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Derrick" wrote in message ... I need to find the minimum non-zero 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Minimum value look up | Excel Worksheet Functions | |||
minimum and the corresponding value | Excel Worksheet Functions | |||
How to lookup the minimum, 2nd minimum and 3rd minimum......... | Excel Worksheet Functions | |||
Minimum but One?? | Excel Worksheet Functions | |||
Minimum Value | Excel Worksheet Functions |