A formula that autofills by trend rather than linear

  • Thread starter Thread starter Arlen
  • Start date Start date
A

Arlen

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