I have a mortgage calculator which calculates the outstanding amount on a
daily basis. This gives me a huge column of figures, which eventually turns
negative somewhere around the middle, depending on how much I am repaying!

I would like to put a single cell at the top of the spreadsheet which
identifies the 'Mortgage Liberation Day" by referencing the cell where the
outstanding balance drops below zero.

I've tried VLOOKUP, but can't seem to get the formula right. Any help will
be much appreciated! - Column A is entirely formatted as dates, and column F
is the outstanding balance.

If you really mean BELOW zero (not equal to zero):

=INDEX(A2:A50,MATCH(0,F2:F50,-1)+1)

If you mean <= zero, test for 0 and go one row up if necessary

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Dr. Zhivago" <Dr. wrote in message ...
|I have a mortgage calculator which calculates the outstanding amount on a
| daily basis. This gives me a huge column of figures, which eventually turns
| negative somewhere around the middle, depending on how much I am repaying!
|
| I would like to put a single cell at the top of the spreadsheet which
| identifies the 'Mortgage Liberation Day" by referencing the cell where the
| outstanding balance drops below zero.
|
| I've tried VLOOKUP, but can't seem to get the formula right. Any help will
| be much appreciated! - Column A is entirely formatted as dates, and column F
| is the outstanding balance.

Well, of course, as SOON as I posted my query, I found the answer (after
several hours of searching earlier!). If it's of help to anyone else, I've
pasted it here, and my very great thanks fo to N HARKAWAT for resolving my
own problem!

Answer
=INDEX(A4:A1000,MATCH(TRUE,(B4:B1000<0),0))
array entered(ctrl+shift+enter)

assuming range a4:a1000 holds the dates and b4:b1000 has the amounts




"JNW" wrote in message
...
I am doing some mortgage calculations. Based on any number of variables
and
calculations the final payoff date can vary. I have a spreadsheet with
the
date (in mmm-yy form) in one column and another with the remaining amount
on
the mortgage (which is a negative amount until payed off then turns
positive). What I need is some way funtion to return in cell a1 the date
at
which the value turns positive.

Thank you

"Dr. Zhivago" wrote:

I have a mortgage calculator which calculates the outstanding amount on a
daily basis. This gives me a huge column of figures, which eventually turns
negative somewhere around the middle, depending on how much I am repaying!

I would like to put a single cell at the top of the spreadsheet which
identifies the 'Mortgage Liberation Day" by referencing the cell where the
outstanding balance drops below zero.

I've tried VLOOKUP, but can't seem to get the formula right. Any help will
be much appreciated! - Column A is entirely formatted as dates, and column F
is the outstanding balance.