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

excelexcel-formulavba

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.

Best Answer

The match function returns a range index

=MATCH(matchValue, 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

Related Topic