first and last occurance

[Darius]

in range a2:a3500 how to find the row number of first cell which its content
value is less than 0.0 and last value which is less than 0.0?

They can be set in B2 and B3.
Thanks for help

 

[Darius]

Thanks but the first eqiuation bounce me back with #N/A and the second
equation give me the last occurance of value less than zero but not the row
number which it is located.

 

[Darius]

ok both formula works and give some numbers but not the right one. I am using
this:

=INDEX(E2:E3286,MATCH(TRUE,E2:E3286<0),1)
Ctrl+Enter+Shift

and:


=LOOKUP(2,1/(E2:E3286<0),E2:E3286)

Ctrl+Enter+Shift

beyond wrng data still have problem with row number of the values these two
equation pick

 

[Bernd P]

Hello Darius,

Array-enter
=1+MATCH(TRUE,A2:A3500<0,0)
resp.
=LOOKUP(2,1/(A2:A3500<0),ROW(A2:A3500))

Regards,
Bernd

 

[Darius]

Thanks the location works fine based on your equation but the below formula
does not find the first value in the range which is less than 0.0?/

=INDEX(E2:E3286,MATCH(TRUE,E2:E3286<0),1)

 

[Bernd P]

Hello Darius,

That's because one param is missing for MATCH.

Array-enter:
=INDEX(E2:E3286,MATCH(TRUE,E2:E3286<0,0),1)

Regards,
Bernd

 

[Darius]

Perfect thanks