looking for a If Then formula i think

[carrguy01]

i will try this question again. Sense i cant figure out how to show my spreadsheet.

i have a amortization schedule running and the following is a break down of my spread sheet

a13 thru 373 these are cells that show instalments
f13 thru thru 373 these are cells that show balance remaing on the loan
I6 is the cell i want the instalment number that reflects when the loan is paid off

The number in cell I6 changes depending on the term of the mortgage and if there was a lump sum paid on it. I think i am looking for a IF THEN command. I will try to illustrate this below
a13 thru 373 e13 thru e373 f13 thru f373
instaments extra payment current balance
1 $249317
2 $248632
3 $18000 $ 229946
continues on untill line 227 instalment # is 215
215 $0
this instalment number can change depending on when a lump sum was made. i want the instalment number displayed in cell I6 at what ever instalment number = $0 current balance.

I hope someone understands what i am looking for!!

 

[carrguy01]

please someone take a look

 

[Andrew Fergus]

How about this:

=MATCH(0,F13:F373,-1)

Andrew

 

[carrguy01]

andrew it works!! i am not sure what it is but i love it. Can i ask one more question? I am trying to figure out this VBHTMLMaker.xlanso i can post here and show my worksheet and i cant figure it out

 

[Andrew Fergus]

The match formula tries to find the nearest matching value. In this case we are searching for the value zero in the range F13:F373. It returns the number of the record that it matched - so a match on row 13 returns the value 1, a match on row 14 returns 2 and so on. Check out the match function in the help file - it is a very handy function that can be used in lots of different situations.

I'm not familiar with VBHTMLMaker - have you tried the tool per the FAQ (complete with instructions) from here :
http://www.mrexcel.com/forum/showthread.php?t=515787

Andrew