Extend Rows and related formula downwards using functions not macros

I have a simple worksheet to amortise a loan. I would like to use a
formula to extend the rows downwards to the number of payments (Number
of years x Number of payments per year) using a formula.

For example, if there were 26 payments per year over 1 year, there
should be 26 rows. Now if I change the number of years to 2 years, two
things should happen:

1. The number of rows should expand to 52 from 26
2. The sum of the interest paid should include these extra rows.i.e
=Sum(rownumber 1 to row number 52) instead of Sum (rownumber 1 to
rownumber 26).

Currently I am using a simple formula along the lines of
Row 1 formula = 1
Row 2 formula = IF(Row 1>=Term,"",Row1+1)

This just provides me with the number of payments and then I copy the
formulas to calcualte the interest and other things down.

Thanks in advance, NR

I take it from when you posted before you got a macro solution,
or no answer ?

You can't extend downward with a function, a function can't change
the content of any cell including itself, it can only return a value.

See Chip Pearson's page
Functions as Opposed to Macros (from his topic.htm)
http://www.cpearson.com/excel/differen.htm

Perhaps you can have your functions return null strings so that
they appear empty, but that is probably not possible or not feasible,
but then don't know what you really have currently.

The microsoft.public.excel.worksheetfunctions is defunct, it has been
renamed to microsoft.public.excel.worksheet.functions
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"NewRipper" <whats@mail.com> wrote in message news:1120777359.477098.323390@o13g2000cwo.googlegroups.com...
> I have a simple worksheet to amortise a loan. I would like to use a
> formula to extend the rows downwards to the number of payments (Number
> of years x Number of payments per year) using a formula.
>
> For example, if there were 26 payments per year over 1 year, there
> should be 26 rows. Now if I change the number of years to 2 years, two
> things should happen:
>
> 1. The number of rows should expand to 52 from 26
> 2. The sum of the interest paid should include these extra rows.i.e
> =Sum(rownumber 1 to row number 52) instead of Sum (rownumber 1 to
> rownumber 26).
>
> Currently I am using a simple formula along the lines of
> Row 1 formula = 1
> Row 2 formula = IF(Row 1>=Term,"",Row1+1)
>
> This just provides me with the number of payments and then I copy the
> formulas to calcualte the interest and other things down.
>
> Thanks in advance, NR
>

actually it might be easier to white out the font on the rows you don't
want with Conditional Formatting, but Print B&W might show everything
anyway.


"David McRitchie" <dmcritchie@msn.com>
> wrote in message > Perhaps you can have your functions return null strings so that
> they appear empty, but that is probably not possible or not feasible,
> but then don't know what you really have currently.