Lookup Formula for "When Negative ... "

  • Thread starter Thread starter j
  • Start date Start date
J

j

Hello,

I have an amortization "what-if" schedule, and on the summary page, I am
trying to write a lookup formula that will return the date from the table
that the balance flips from positive to negative. For example, the formula
would return 4-1-2001 in the following case (in reality there are a lot of
columns in between)


DATE BALANCE
1-1-2001 29
2-1-2001 19
3-1-2001 09
4-1-2001 -1
5-1-2001 -11
6-1-2001 -21


Thanks!
 
Okay, I added a column with a formula

=IF(AND(F18<0,F17>0),"payoffperiod","")

And I use a simple vlookup to pickup that period from the table. If anyone
knows a simpler way to do this without the extra column, I would still be
interested just from a learning perspective, and to clean up my approach.

Thanks!
 
Back
Top