Prev: TextBox format?
Next: Can I random sample from a set until every sample is selected?
From: Nikhil on 25 Apr 2010 15:27
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
From: Billy Liddel on 25 Apr 2010 15:55
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
From: Teethless mama on 25 Apr 2010 16:26
=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
 | 
Pages: 1
Prev: TextBox format?
Next: Can I random sample from a set until every sample is selected?