# look up first negative number, but

#### wenzhaoxie

##### New Member
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......

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Lewiy

##### Well-known Member
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

##### New Member
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

##### Banned
=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

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

#### wenzhaoxie

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

#### wenzhaoxie

##### New Member
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

##### New Member
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

##### Well-known Member
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

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

Replies
0
Views
1K
Replies
9
Views
325
Replies
1
Views
264
Replies
6
Views
391
Replies
2
Views
286

1,190,920
Messages
5,983,590
Members
439,852
Latest member
balasat

### 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.

### Which adblocker are you using?

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

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