I have a (I hope) simple question regarding the INDEX function. I have a
spreadsheet that calculates a mortgage schedule similar to this(some columns
have been deleted to get it into this space):

No. Payment Date Ending Balance
1 9/1/2008 $78,196.73
2 10/1/2008 78,122.08
3 11/1/2008 78,047.04
4 12/1/2008 77,971.60
5 1/1/2009 77,895.78
6 2/1/2009 77,819.56
7 3/1/2009 77,742.94
8 4/1/2009 77,665.92
etc.

I would like to find the payment number (column A) where the ending balance
is <=0

Any help would be appreciated.

Thanks

Iriemon

find the payment number (column A) where the ending balance is <=0

Assuming data as posted in cols A to C
In say, E2, normal ENTER to confirm:
=INDEX(A2:A100,MATCH(TRUE,INDEX(C2:C100<=0,),0))
Adapt to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Iriemon" wrote:
I have a (I hope) simple question regarding the INDEX function. I have a
spreadsheet that calculates a mortgage schedule similar to this(some columns
have been deleted to get it into this space):

No. Payment Date Ending Balance
1 9/1/2008 $78,196.73
2 10/1/2008 78,122.08
3 11/1/2008 78,047.04
4 12/1/2008 77,971.60
5 1/1/2009 77,895.78
6 2/1/2009 77,819.56
7 3/1/2009 77,742.94
8 4/1/2009 77,665.92
etc.

I would like to

=INDEX(A:A,MAX(IF((C2:C200<"")*(C2:C200<=0),ROW(C 2:C200))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Iriemon" wrote in message
...
I have a (I hope) simple question regarding the INDEX function. I have a
spreadsheet that calculates a mortgage schedule similar to this(some
columns
have been deleted to get it into this space):

No. Payment Date Ending Balance
1 9/1/2008 $78,196.73
2 10/1/2008 78,122.08
3 11/1/2008 78,047.04
4 12/1/2008 77,971.60
5 1/1/2009 77,895.78
6 2/1/2009 77,819.56
7 3/1/2009 77,742.94
8 4/1/2009 77,665.92
etc.

I would like to find the payment number (column A) where the ending
balance
is <=0

Any help would be appreciated.

Thanks

Iriemon

=INDIRECT(ADDRESS(MATCH(0,C:C,-1),1,4,1))
Regards,
Stefi

€žIriemon€� ezt Ã*rta:

I have a (I hope) simple question regarding the INDEX function. I have a
spreadsheet that calculates a mortgage schedule similar to this(some columns
have been deleted to get it into this space):

No. Payment Date Ending Balance
1 9/1/2008 $78,196.73
2 10/1/2008 78,122.08
3 11/1/2008 78,047.04
4 12/1/2008 77,971.60
5 1/1/2009 77,895.78
6 2/1/2009 77,819.56
7 3/1/2009 77,742.94
8 4/1/2009 77,665.92
etc.

I would like to find the payment number (column A) where the ending balance
is <=0

Any help would be appreciated.

Thanks

Iriemon

THANKS MAX, just what I was searching for!

"Max" wrote:

find the payment number (column A) where the ending balance is <=0

Assuming data as posted in cols A to C
In say, E2, normal ENTER to confirm:
=INDEX(A2:A100,MATCH(TRUE,INDEX(C2:C100<=0,),0))
Adapt to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Iriemon" wrote:
I have a (I hope) simple question regarding the INDEX function. I have a
spreadsheet that calculates a mortgage schedule similar to this(some columns
have been deleted to get it into this space):

No. Payment Date Ending Balance
1 9/1/2008 $78,196.73
2 10/1/2008 78,122.08
3 11/1/2008 78,047.04
4 12/1/2008 77,971.60
5 1/1/2009 77,895.78
6 2/1/2009 77,819.56
7 3/1/2009 77,742.94
8 4/1/2009 77,665.92
etc.

I would like to

=LOOKUP(2,1/((C2:C100<=0)*(C2:C100<"")),A2:A100)


"Iriemon" wrote:

I have a (I hope) simple question regarding the INDEX function. I have a
spreadsheet that calculates a mortgage schedule similar to this(some columns
have been deleted to get it into this space):

No. Payment Date Ending Balance
1 9/1/2008 $78,196.73
2 10/1/2008 78,122.08
3 11/1/2008 78,047.04
4 12/1/2008 77,971.60
5 1/1/2009 77,895.78
6 2/1/2009 77,819.56
7 3/1/2009 77,742.94
8 4/1/2009 77,665.92
etc.

I would like to find the payment number (column A) where the ending balance
is <=0

Any help would be appreciated.

Thanks

Iriemon

Hi Max
its ok but if i have 3 value <=0 in c1 ,c4, c5 and i need answer like 1&4&5.
can it possibel

Thanks

"Max" wrote:

find the payment number (column A) where the ending balance is <=0

Assuming data as posted in cols A to C
In say, E2, normal ENTER to confirm:
=INDEX(A2:A100,MATCH(TRUE,INDEX(C2:C100<=0,),0))
Adapt to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Iriemon" wrote:
I have a (I hope) simple question regarding the INDEX function. I have a
spreadsheet that calculates a mortgage schedule similar to this(some columns
have been deleted to get it into this space):

No. Payment Date Ending Balance
1 9/1/2008 $78,196.73
2 10/1/2008 78,122.08
3 11/1/2008 78,047.04
4 12/1/2008 77,971.60
5 1/1/2009 77,895.78
6 2/1/2009 77,819.56
7 3/1/2009 77,742.94
8 4/1/2009 77,665.92
etc.

I would like to

Posted an alternative set-up
for you in your other thread in .misc:
http://tinyurl.com/c6kn96
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"ADIB" wrote in message
...
Hi Max
its ok but if i have 3 value <=0 in c1 ,c4, c5 and i need answer like
1&4&5.
can it possibel

Welcome, great to hear.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Iriemon" wrote in message
...
THANKS MAX, just what I was searching for!