ExcelBanter - last negative cell

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - last negative cell (https://www.excelbanter.com/excel-discussion-misc-queries/262379-last-negative-cell.html)

Nikhil

last negative cell

Hi.
I have a column that contain numbers (either + or -). I want to find the
last cell that contains a -ve value and return the corresponding date from
the adjoining column

e.g

Col A Col B
1-Apr-2010 -1000
2-Apr-2010 -1000
3-Apr-2010 +500
4-Apr-2010 -1000
5-Apr-2010 +700

In this illustration, i did like to have the value 4-Apr-2010 returned

Can anyone please help

Regards

Nikhil

Billy Liddel

last negative cell

Try this array formula (Entered with CTRL + SHFT + ENTER)

=INDEX(A:A,MATCH(MAX(B2:B65536<0),B2:B65536)+1)

Regards
Peter

"Nikhil" wrote:

Hi.
I have a column that contain numbers (either + or -). I want to find the
last cell that contains a -ve value and return the corresponding date from
the adjoining column

e.g

Col A Col B
1-Apr-2010 -1000
2-Apr-2010 -1000
3-Apr-2010 +500
4-Apr-2010 -1000
5-Apr-2010 +700

In this illustration, i did like to have the value 4-Apr-2010 returned

Can anyone please help

Regards

Nikhil

Teethless mama

last negative cell

=LOOKUP(2,1/(B1:B100<0),A1:A100)

"Nikhil" wrote:

Hi.
I have a column that contain numbers (either + or -). I want to find the
last cell that contains a -ve value and return the corresponding date from
the adjoining column

e.g

Col A Col B
1-Apr-2010 -1000
2-Apr-2010 -1000
3-Apr-2010 +500
4-Apr-2010 -1000
5-Apr-2010 +700

In this illustration, i did like to have the value 4-Apr-2010 returned

Can anyone please help

Regards

Nikhil

All times are GMT +1. The time now is 12:11 AM.