count the row from my vlookup number to zero

Hi,

i have a function being able to call the smallest (negative number) number in the range of data (down the column). Now i am trying to count how many rows it takes from Zero down to my negative number. how should i set the formula?

i am working in worksheet #2, i am thinking to vlookup the number in worksheet 1, and then trying to figure out which function to use to make it move up until it hits the first zero. i can't just find the zero in the range, coz there are other zeros in the range.

thanks.
madison.

To find the row number of the smallest number in column A

=MATCH(MIN(A:A),A:A,0)

[note: if the smallest number is duplicated this gives the row number of the first occurrence]

to find the first zero in column A

=MATCH(0,A:A,0)

so to find the number of rows between

subtract one from the other, i.e.

=MATCH(MIN(A:A),A:A,0)-MATCH(0,A:A,0)

thanks for your reply. it's not working for me as i am not trying to find the first zero. usually the columns go as attached. thanks for your help. coudl you please see if other way can solve it?

madison.

So you want to find the number of rows between the lowest value in the column and the next zero up from that lowest value.

Try

=MATCH(MIN(D1:D100),D1:D100,0)-MATCH(2,1/(OFFSET(D1,,,MATCH(MIN(D1:D100),D1:D100,0)-1)=0))

confirmed with CTRL+SHIFT+ENTER

yes, as the lowest value will be at different place and there are many zeros in the column, i am trying to count how many months it takes from the zero go to the lowest return (although there may be other negative value in between)

i tried the formula:


=MATCH(MIN(B2:B57),B2:B57,0)-MATCH(2,1/OFFSET(B2,,,MATCH(MIN(B2:B57),B2:B57,0)-1)=0)
after pressing crtl + shift +enter get { } in the beginning and end....

but not working.....i don't know what went wrong...

thank you for your help.

Sorry, my fault, a couple of parentheses went missing in that last one, try this

=MATCH(MIN(D2:D57),D2:D57,0)-MATCH(2,1/(OFFSET(D2,,,MATCH(MIN(D2:D57),D2:D57,0)-1)=0))

also confirmed with CTRL+SHIFT+ENTER

apologize for not getting it......

i tried the formula.....but it gave me error.....

could you tell me why you put 2 in the first argument for the following Match function?

MATCH(2,1/(OFFSET(D2,,,MATCH(MIN(D2:D57),D2:D57,0)-1)=0))

and, why did you put 1/offset( ) as the 2nd argument?

and, what is the =0 used for?

thanks a lot...apologize for so many questions....

madison