How to change drag increments.

[abowden]

I am doing a simple summation task for 70 years of data. Each year has 12 things that need to be added together.

Is there a way I can write something like "= sum(B2:B13)", and then drag down so the boxes below look like:

"= sum(B2:B13)"
"= sum(B14:B25)"
"= sum(B26:B37)"
...

when I try this, excel increases every "B" by 1, I need to to increase the "b's" by 12.

Thanks

[DonkeyOte]

You can use INDEX

First formula

Please Login or Register  to view this content.

You can also use OFFSET but this is Volatile (see link in sig. for more info)

Please Login or Register  to view this content.

[abowden]

cool thanks, anyway you can explain each part of the index function. It works when I have everything set up hypothetically starting in A1, but my spreadsheet is a little more complicated, and I need to change the location of certain parts of that line.

[DonkeyOte]

change the references to A$1 to be that of the cell which contains the first formula.

The INDEX is used to create a range, ie

INDEX:INDEX

which can in turn be worked with like any other range, ie

=SUM(INDEX(A:A,1):INDEX(A:A,10)) is akin to SUM(A1:A10)

In your case the starting row of the range is determined by number of formulae processed thus far * 12 plus 2 (first row of results to be summed)

So for first formula you do not have any prior calcs so

INDEX(B:B,2+(12*(# of prior calcs)))

In the first calc prior calcs should be = 0 thus 2 + 0 thus B2

In the next row below prior calc will become 1 thus 2 + (12*1) thus B14

The closing row is determined by 1 + number of calcs up to and including current * 12, so for first formula

INDEX(B:B,1+(12*# of calcs incl.))

In the first calc this should result in 13 -> 1 + (12*1) -> thus B13

In the next row the # of calcs increments and therefore -> 1 + (12*2) -> B25

The count of calculations is conducted using the ROWS( ) function with a mixture of absolute & relative referencing in the range used within - see XL Help for more info. on both these subjects.