look up first negative number, but

wenzhaoxie

New Member
Joined
Aug 15, 2007
Messages
9
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Upvote 0
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.
 
Upvote 0
=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.
 
Upvote 0
xld – I don’t think that works if the last number before the negative sequence is a zero. Nice job though!
 
Upvote 0
Thanks, XLD. What does "committed" here mean? I guess I can't simply copy your formula into my spreadsheet, right?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
actually it works with Ctr+Alt+Enter. I changed the reference cells and changed "row" to "column".....Great, thanks a lot.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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