A formula that autofills by trend rather than linear
 

I am calculating the interest on a 20-year mortgage.

Sheet1, Col A contains the interest paid in each of the 240 months.

Sheet2, Col A sums up the total interest paid in each of the 20 years.

Cell A1 in Sheet2 should contain the formula SUM(Sheet1!A1:A12) for year 1,
then A2 should be SUM(Sheet1!A13:A24), etc...

If I autofill this formula down, however, Excel goes from range A1:A12 to
range A2:A13. I need the references to jump in blocks of 12. How do I set
Excel straight on this matter?

Thank you for any help.

A formula that autofills by trend rather than linear
 

=SUM(INDEX(Sheet1!A:A,ROW(A1)*12-11):INDEX(Sheet1!A:A,ROW(A1)*12))

Copy down until you get zeros showing up.


Gord Dibben MS Excel MVP

On Mon, 9 Feb 2009 20:20:02 -0800, Arlen
wrote:

I am calculating the interest on a 20-year mortgage.

Sheet1, Col A contains the interest paid in each of the 240 months.

Sheet2, Col A sums up the total interest paid in each of the 20 years.

Cell A1 in Sheet2 should contain the formula SUM(Sheet1!A1:A12) for year 1,
then A2 should be SUM(Sheet1!A13:A24), etc...

If I autofill this formula down, however, Excel goes from range A1:A12 to
range A2:A13. I need the references to jump in blocks of 12. How do I set
Excel straight on this matter?

Thank you for any help.