Find lowest negative and positive value in a row

[Heiner]

Hi all,

I know this problem has been answered more than once, but the solutions I found so far don't work...

The task:
I have a row of values that starts with large negative numbers and in each column the values become smaller until they eventually become positive. I need to automatically find write the lowest negative number and the lowest positive number in this row into specific cells.

For example:
-10, -5, -2, 1, 10 --> I'm looking for the "-2" and "1" value to be written in separate cells

Any help would be appreciated. I tried Index Match, but failed with the "look for the smallest neg. in this row"... I'm a bit rusty when it comes to Excel


Thanks for your support
Heiner

 

[steve the fish]

Hi. These need entering CTRL-SHIFT-ENTER not just ENTER.

=MAX(IF(A1:E1<0,A1:E1))
=MIN(IF(A1:E1>0,A1:E1))

 

[Heiner]

Wow, thanks for the fast reply. That may have been the problem all along

These need entering CTRL-SHIFT-ENTER not just ENTER.

One additional quesiton....

Is it possible to find the smallest positive number and then write the left hand cell value into a specific cell (so find "1" from the example from that and then write the value of the cell right next to it on the left side into another cell?

Reason for this is, that I actually made a mistake. I'm not looking for the smallest negative, but the last negative number in the row. It is a ROI question so sometime investments run for more than a year without return coming in. In such cases the smallest negative is in year one, but I'm looking for the tipping point between negative to positive, which may only be in year 4 or so...


Thanks so much for this fast response.

 

[steve the fish]

This will give you the last negative number:

=LOOKUP(2,1/(A1:E1<0),A1:E1)

 

[Heiner]

Steve the fish - thank you

the combination of the LOOKUP and MAX formular work just as I need them to.

Could you explain briefly what the

2,1/(A1:E1<0)

part does in the function? It works, but I don't understand what it does and it doesn't seem to explain it in the function support...

Thanks

 

[steve the fish]

This part, 1/(A1:E1<0), is going to give an array of values that are a test if the value in the range is less than zero so in your example:

{-10,-5,-2,1,10}

becomes

{1,1,1,#DIV/0!,#DIV/0!}

as to excel 1/TRUE = 1 and 1/FALSE = #DIV/0!

Crucially the LOOKUP function ignores errors and 1 divided by TRUE or FALSE can never be 2. So we search for 2 so the lookup will be searching for the last 1 or TRUE. Hope that makes some sort of sense.

<tbody>
[TD="width: 93, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]

</tbody>

 

[Heiner]

Thanks you so much, this makes perfect sense. I wouldn't have figured it out without the explanation though.

I tried to apply it to find the first positive value, but that gave me the last negative number (identical result from your formula) for the "lookup value" "0" or the largest value in the row for "lookup value" of "1". My problem is solved, this is just for my understanding now

I used this formula: "=LOOKUP(1;1/(L184:V184>0);L184:V184)"

 

[steve the fish]

Heres one that could find 1st positive number:

=INDEX(A1:E1,MATCH(1,INDEX(--(A1:E1>0),0),0))

 

[Heiner]

Perfect. Thanks you so much.