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.


"Mike H" wrote:

Hi,

To find the first value less than zero use this array formula

=INDEX(A1:A1000,MATCH(TRUE,A1:A1000<0,0),1)

To find the last negative number use this array formula

=LOOKUP(2,1/(A1:A1000<0),A1:A1000)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike





"Darius" wrote:

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