Excel formula to determine cell id when a series of numbers becomes negative

Question

Data examples

     A            B
1  Date        Amount
2  Apr 1        $6,000
3  May 1        $4,250
4  June 1       $2,750
5  July 1       $1,000
6  Aug 1       -$0.075   <- This Cell/Row
7  Sept 1     -$0.2500

In a column of numbers (actually 100-200 rows), when the value changes to negative, for example. if it is we owed for the loan when the loan is repaid. Please note that the real difference between the numbers varies depending on interest, taxes, one-time payments, etc. Therefore, I can’t just calculate (amount / payment) = number of months.

Is there a way to use Excel formulas to determine this? This may be the case when VBA is required (this is normal), but if I can avoid it, I would like to.

Answer 1

=MATCH(matchValeu, range, matchType: 0=exact, 1=greater than, -1=less than


=MATCH(0, B2:B7, -1)

, 0 B2: B7. 5

Answer 2

MATCH, .

=MATCH(lookup value, lookup range, lookup type)
=MATCH(0,B1:B7,-1)

-1, . , 0.

5. 6, .

=MATCH(0,B1:B7,-1)+1

, ADDRESS.

=ADDRESS(Row number, Column number)
=ADDRESS(MATCH(0,B1:B7,-1)+1,2)

$B $6

, . OFFSET.

=OFFSET(A1,MATCH(0,B1:B7,-1),0)
=OFFSET(A1,MATCH(0,B1:B7,-1),1)

A6, Aug 1

B6, - $0.075

Answer 3

, .

, :

=IF(YOUR_CELL_ACTUAL_FORMULA < 0 , 0, YOUR_CELL_ACTUAL_FORMULA)

, , :

=MATCH(0, YOUR_AMOUNT_RANGE, -1)

, .