Lookup Formula for "When Negative ... "
 

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!

Lookup Formula for "When Negative ... "
 

Okay, I added a column with a formula

=IF(AND(F18<0,F170),"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!



"j" <j@j wrote in message ...
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!