Mortgage calculator: finish date?

[Dr. Zhivago]

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.

 

[Niek Otten]

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

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

 

[Dr. Zhivago]

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