first positive value return date

J

JNW

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
 
N

N Harkawat

=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
 
N

N Harkawat

change the less than sign < to > greater than
so the correct formula is
=INDEX(A4:A1000,MATCH(TRUE,(B4:B1000>0),0))
array entered(ctrl+shift+enter)
 
A

Aladin Akyurek

=INDEX($A$2:$A410,MATCH(TRUE,$B$2:$B$10>=0,0))

which must be confirmed with control+shift+enter, not just with enter.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top