First negative number
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 |
First negative number
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 |
First negative number
=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 |
First negative number
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 |
All times are GMT +1. The time now is 10:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com