Index Question

[Iriemon]

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

 

[Max]

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

 

[Bob Phillips]

=INDEX(A:A,MAX(IF((C2:C200<>"")*(C2:C200<=0),ROW(C2: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)

 

[Stefi]

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

„Iriemon†ezt írta:

 

[Iriemon]

THANKS MAX, just what I was searching for!

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

 

[Teethless mama]

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

 

[ADIB]

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]

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

 

[Max]

Welcome, great to hear.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik