Find lowest negative and positive value in a row

Heiner

New Member
Joined
Apr 30, 2018
Messages
5
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 :mad:


Thanks for your support
Heiner
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi. These need entering CTRL-SHIFT-ENTER not just ENTER.

=MAX(IF(A1:E1<0,A1:E1))
=MIN(IF(A1:E1>0,A1:E1))
 
Upvote 0
Wow, thanks for the fast reply. That may have been the problem all along
These need entering CTRL-SHIFT-ENTER not just ENTER.
(y)(y)(y)


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.
 
Upvote 0
This will give you the last negative number:

=LOOKUP(2,1/(A1:E1<0),A1:E1)
 
Upvote 0
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
 
Upvote 0
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>
</tbody>
 
Upvote 0
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)"
 
Upvote 0
Heres one that could find 1st positive number:

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

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top