Finding a row based on a random value in a column

[bobzebra]

First, apologies if this has been answered before but I couldn't find
anything close in the forums:

My problem is I need to find the first row reference if a value in one
of the columns is (unpredictably) less than 0. I have tried a mix of
Index, Match and the Excel lookup wizard but to no avail.

For example, at its simplest I have 2 columns x and y

A B
1 x y
2 1 13
3 2 6
4 3 0
5 4 -1
6 5 -12
7 6 4
etc

how do I locate the first row when the y column goes negative. I know
it is simple if I know beforehand ( in this case it is row cell A5) ,
but if the Y column varies at random, I get stuck. Any help much
appreciated
Bob

bob@zebragraphics.co.uk

[Biff]

Hi!

One way:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(COUNTIF(B2:B7,"<0"),MATCH(TRUE,B2:B7<0,0)+1,"")

Biff

"bobzebra" <bob@zebragraphics.co.uk> wrote in message
news:1135450651.690929.169260@g14g2000cwa.googlegroups.com...
> First, apologies if this has been answered before but I couldn't find
> anything close in the forums:
>
> My problem is I need to find the first row reference if a value in one
> of the columns is (unpredictably) less than 0. I have tried a mix of
> Index, Match and the Excel lookup wizard but to no avail.
>
> For example, at its simplest I have 2 columns x and y
>
> A B
> 1 x y
> 2 1 13
> 3 2 6
> 4 3 0
> 5 4 -1
> 6 5 -12
> 7 6 4
> etc
>
> how do I locate the first row when the y column goes negative. I know
> it is simple if I know beforehand ( in this case it is row cell A5) ,
> but if the Y column varies at random, I get stuck. Any help much
> appreciated
> Bob
>
> bob@zebragraphics.co.uk
>

[Ragdyer]

Since you mentioned A5, are you looking for the return to come from Column
A?
If so, try this *array* formula:

=INDEX(A1:A7,MATCH(TRUE,B1:B7<0,0))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"bobzebra" <bob@zebragraphics.co.uk> wrote in message
news:1135450651.690929.169260@g14g2000cwa.googlegroups.com...
> First, apologies if this has been answered before but I couldn't find
> anything close in the forums:
>
> My problem is I need to find the first row reference if a value in one
> of the columns is (unpredictably) less than 0. I have tried a mix of
> Index, Match and the Excel lookup wizard but to no avail.
>
> For example, at its simplest I have 2 columns x and y
>
> A B
> 1 x y
> 2 1 13
> 3 2 6
> 4 3 0
> 5 4 -1
> 6 5 -12
> 7 6 4
> etc
>
> how do I locate the first row when the y column goes negative. I know
> it is simple if I know beforehand ( in this case it is row cell A5) ,
> but if the Y column varies at random, I get stuck. Any help much
> appreciated
> Bob
>
> bob@zebragraphics.co.uk
>

[bobzebra]

Guys: brilliant many thanks; works a treat. More importantly you have
opened door on Array formulas. I simply hadn't reaslied they existed in
Excel. But I suppose without a first example it doesn't naturally occur
to you that they may solve your problem.
thanks again.

Bob