Display result based on another cell

I would like to display a date where the value of another cell is 0.

I have created a very basic spreadsheet to calculate mortgage repayments and work out repayment date based on payments made. eg...

Cell A2 = the date
Cell B2 = starting balance for that day
Cell C2 = any interest for that day
Cell D2 = any payments for that day
Cell E2 = closing balance for that day (B2+C2-D2)
Cell A3 = E2 and the process above is replicated for 800 rows

I would like to achieve 2 things:

firstly stop the calculations once the figure in column e = 0 and the amount has been paid off
Secondly I would like the date corresponding to the date the balance in column E = 0 to be displayed in Cell A1

Really appreciate your help

(1) Wrap an IF around the calculations to check if it should go or no-go.

B3 = IF($E2 > 0, do_the_calculation, "")

Then pull across and down

(2) Assuming that the data in E:E is basically in descending order, use MATCH to find the zero point and INDEX to find the date of the day that happened.

A1 = Index( a2:a800, MATCH(0, e2:e800, -1) )

thanks Ben,

Worked a treat. I have one more query if I may....
Id like to format all cells after the end date where the result in column e = 0 to be white with white text so they appear hidden.

Appreciate your help

That's not really the best solution to making those cells nicely appear blank...

The preferred method (well, preferred over conditional formatting by me and some other users around here) is using an IF(check, do_something, "")

where the "" term in the FALSE condition will fill the cell with a text string of length 0, containing no characters.

This is the approach I took for that (1)...

Of course, since ("" > 0) evaluates as TRUE, you'd have to change the logical to handle that, with like

B3=IF( OR( E2 > 0, E2 = ""), go, "")

to account for having a "blank" cell.

That being said,

To have a cell with zero in it turn the color of the text white, that's just conditional formatting, =0, with custom format (white text / no fill or white fill). I'd have to think about how to format one cell based on the value of another cell....