# 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.

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