Find 0 or smallest negative number from range

HI,
I am trying to write code that will locate either a 0 or the smallest negative number in a range, eg,

example 1,
8, 6, 2, 0, -0.5, -2
the result would be 0

example 2,
8, 6, 2, -0.5, -2
the result would be -0.5

It is driving me nuts !
Thanks in advance,
Andy

is -0.5 smaller than -2?

if you mean you want -2, then try the Min() function.

e.g =Min(A1:A10)

if it actually is that you want the closest to 0 value... (i.e. -0.5)

you can perhaps try:

=MAX(IF(A1:A10<=0,A1:A10),MIN(A1:A10))

confirmed with CTRL+SHIFT+ENTER, not just ENTER

After you enter the formula in, with the proper ranges, hold the CTRL and SHIFT keys down, then press ENTER. You will see { } brackets appear around it.

What if there are no zero or negs...? This formula will return smallest positive number.

With your sample data in Col_A, beginning in A1

This regular formula returns zero, if it is in the list,
otherwise it returns the negative value that is closest to zero:

Please Login or Register  to view this content.

Note: If no values are less than or equal to zero, it returns #N/A!

Is that something you can work with?