A formula that autofills by trend rather than linear

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.
 
G

Gord Dibben

=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A formula that autofills by trend, not linear 3
Excel repatation of Formula 8
How to maintain a set range of cells when dragging a formula (e.gmonthly>annual data) 2
Referencing a tab NUMBER rather than the tab NAME in a formula 1
How can I protect Cells containing a formula? 1
I am looking for a formula which extracts a list from data in sheet1to sheet2 filtered by a starting 1
Operating on 'blocks' of data 1
Problem creating a logarithmic trend line 2

Top