I've got a spreadsheet with a list of values in col "A" sorted in descending
(not ascending) order. Col "B" has a date. I need a formula that returns the
date the value in col "A" first turns into a negative number. In the sample
below it should return the date Jan 07

Thanks

100 Oct 06
30 Nov 06
10 Dec 06
-10 Jan 07
-75 Feb 07

Try entered with Ctrl+Shift+Enter

=INDEX($B$1:$B$5,MATCH(LARGE(IF($A$1:$A$5<0,A1:A5) ,1),$A$1:$A$5,-1))

HTH

"Iriemon" wrote:

I've got a spreadsheet with a list of values in col "A" sorted in descending
(not ascending) order. Col "B" has a date. I need a formula that returns the
date the value in col "A" first turns into a negative number. In the sample
below it should return the date Jan 07

Thanks

100 Oct 06
30 Nov 06
10 Dec 06
-10 Jan 07
-75 Feb 07

=INDEX(B1:B5,MATCH(0,A1:A5,-1)+1)
--
Gary's Student


"Iriemon" wrote:

I've got a spreadsheet with a list of values in col "A" sorted in descending
(not ascending) order. Col "B" has a date. I need a formula that returns the
date the value in col "A" first turns into a negative number. In the sample
below it should return the date Jan 07

Thanks

100 Oct 06
30 Nov 06
10 Dec 06
-10 Jan 07
-75 Feb 07

Try =INDEX(B1:B5,MATCH(0,A1:A5,-1)+1)
Happy Thanksgiving from Canada
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Iriemon" wrote in message
...
I've got a spreadsheet with a list of values in col "A" sorted in
descending
(not ascending) order. Col "B" has a date. I need a formula that returns
the
date the value in col "A" first turns into a negative number. In the
sample
below it should return the date Jan 07

Thanks

100 Oct 06
30 Nov 06
10 Dec 06
-10 Jan 07
-75 Feb 07