first positive value return date

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

=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" <JNW@discussions.microsoft.com> wrote in message
news:7C78225D-AE69-4942-85E7-C4E3412C38B7@microsoft.com...
>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

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)

"N Harkawat" <nh@nh.com> wrote in message
news:O7Zd1hHvFHA.3256@TK2MSFTNGP09.phx.gbl...
> =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" <JNW@discussions.microsoft.com> wrote in message
> news:7C78225D-AE69-4942-85E7-C4E3412C38B7@microsoft.com...
>>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
>
>

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

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

JNW wrote:
> 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