Index Question

  • Thread starter Thread starter Iriemon
  • Start date Start date
I

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
 
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
 
=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)
 
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
 
Back
Top