look up first negative number, but

[wenzhaoxie]

Hi

This may sound simple, but is not (it might be easy for you hopefully)...... I have a string of numbers and the first few numbers can be either postive, negative or zero....Later on, the numbers all become negative......

I need to find the the cell which contains the first negative number in the consequtive negative numbers.....The difficult part is the numbers before the consequtive negative numbers can be anything......

Thanks a lot for your help in advance.

 

[Lewiy]

Welcome to the board!!

Are these numbers arranged in a row or column? Would there ever be an instance where there is more than one consecutive negative number before the sequence you are looking for?

 

[wenzhaoxie]

Thanks for the welcome. These numbers are in a row and yes, there can be consequtive negative numbers before the negative numbers....That's what makes it difficult.

 

[xld]

=IF(INDEX(A1:A1000,MAX(IF(A1:A1000>0,ROW(A1:A1000)))+1)<0,INDEX(A1:A1000,MAX(IF(A1:A1000>0,ROW(A1:A1000)))+1),"No final negative series")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

 

[Lewiy]

xld – I don’t think that works if the last number before the negative sequence is a zero. Nice job though!

 

[wenzhaoxie]

Thanks, XLD. What does "committed" here mean? I guess I can't simply copy your formula into my spreadsheet, right?

 

[wenzhaoxie]

I tried it with your formula on the string, 1, 2, 0, -1, 2, 3, -5, -6, -7, -8, -9, but it returns "no final negative series"....If I were to turn every number to negative, it would return the first negative number......Plus, this only works in a column I think.....Thanks.

 

[wenzhaoxie]

Let's say I want to look for the number -5 (the first negative number of all the consequtive negative numbers) in this string 1, 2, 0, -1, -3, -2, 0, 2, 0, 3, -5, -6, -7, -8, -9, -10, -11

Thanks.

 

[Lewiy]

Did you enter it as an array formula (pressing ctrl+shift+enter instead of just enter when you finish typing it in)?
The example that has been given is designed for a column but you can just change the cell references as appropriate.
However, as I said above, I think if the last number before the negative sequence is a zero, then it won’t work propery.

 

[wenzhaoxie]

actually it works with Ctr+Alt+Enter. I changed the reference cells and changed "row" to "column".....Great, thanks a lot.