Excel formula to determine cell ID when a series of numbers turns negative

Question

Sample data

     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 (in reality 100-200 rows), when the value changes to negative, e.g. if these we're amounts owed on a loan, when the loan would be paid off by. Note the real difference between the numbers fluctuates based on interest, taxes, one-off payments etc. So I can't just count (total / payment) = number of months.

Is there a way to use Excel's formulas to determine this? This may be a case of requiring VBA (which is fine) but if I can avoid it, I'd like to.

Answer 1

The match function returns a range index

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


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

Match the first cell that is less than 0 in range B2:B7. From your sample data this would return 5

Use the Offset function to return a particular cell based on the index value

Answer 2

I'm not sure want you want to do.

If you want to avoid having negative number you can do :

=IF(YOUR_CELL_ACTUAL_FORMULA < 0 , 0, YOUR_CELL_ACTUAL_FORMULA)

If you want to know when the number turn to be negative you can do :

=MATCH(0, YOUR_AMOUNT_RANGE, -1)

This will give you the first line number when the amount is negative.

Answer 3

Use the MATCH formula to determine the row number.

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

You will need to use match type of -1, as your data is in descending order. This setting will return the smallest value that is greater than or equal to the lookup value of 0.

Based on your data, this would return the row number 5. You are expecting to see the row number 6, so the formula needs to be extended as follows.

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

To determine the cell ID you would need to wrap this formula into an ADDRESS formula.

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

This would return the value $B$6

It would probably be more useful to return the related date or value. This can be done with an OFFSET formula.

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

The first formula would return the date in A6, Aug 1

The second formula would return the value in B6, -$0.075