Min Function

[Jimmy Anderson]

I have the following series of numbers in a row in
ascending order: {0,0,0,6.75,7.5,8.0, etc.} I'm trying to
return the smallest value greater than zero (i.e. 6.75).
When using the min function, it obviously returns a 0. If
I change the 0's to "", then another function that I'm
using returns a #VALUE because I'm also using that row to
calculate a date.

I tried HLOOKUP (0.000001, {row}, 1), but that returns the
next largest value that is less than the lookup value
(which gives me the zero again).

Is there a function that returns the minimum value of an
array greater than zero? Or is there a function that
returns the next largest value GREATER THAN the lookup
value? I've searched throught list of functions, but was
unsuccessful.

Thanks for the help.

 

[Aladin Akyurek]

=MIN(IF(A1:A6,A1:A6))

which you need to confirm by hitting control+shift+enter at the same time,
not just by hitting enter.

 

[Lara]

If you copy your series of numbers to a column, you could
filter to show all rows except the zeros, and then use
the following subtotal function:

=SUBTOTAL(5,A1:A100)

 

[acw]

Jimmy

A couple of options.

1) =MIN(VALUE(SUBSTITUTE(A1:F1,0,MAX(A1:F1)))) array
entered (shift, ctrl, enter)

2) =SMALL(A1:F1,COUNTIF(A1:F1,0)+1)


Tony

 

[Aladin Akyurek]

That's a pretty dangerous formula if A1:F1 houses values that not meet the

 

[Roger]

Hello there, Lara. I suggest that you use the "small" function. Thi
will eliminate the need to filter and will give you more immediat
flexibility in the event that things change. That means just in cas
you might later need to find some other value greater than the zer
value- say perhaps the second, third or fourth largest value up fro
zero.

Here's a suggestion and an example: Suppose your numbers that yo
need to analyze are in column A, let's say from A1: to A300. Name thi
range of cellls "Numbers" ( or whatever name you like). Then use th
function : Small( NUMBERS,2). This will find the second number up fro
the smallest number within the range that you have named. You ca
modify the second argument to the function if needed --- Small(NUMBERS
3) will return the third largest number up from the smallest.

Will your numbers always contain a zero? If you're not sure then us
the COUNTIF function to see if a zero is present.Nest these function
if you need to do so. Bye