How do I fill down formulas so they iterate in intervals other th.

[Guest]

We run into this problem all the time. I would like formulas to be able to
"fill down" in a pattern like numbers do. For example, if I enter10, 20, 30
then excel continues the series with 40, 50, 60 etc. However, this does not
work with formulas. If the formula in B1 =SUM(A1:A10) and B2=SUM(A11:A20), I
would like to be able to use that pattern to continue the series where the
next one would be =SUM(A:21:A30). However, because the formulas only appear
iterate in series of one, instead of this value, I get =SUM(A2:A11) instead
in cell B3. The only workaround we've been able to do is to fill down the
first cell plus the 9 blank cells below it and then drag the cells back up to
remove the blank spaces. Is there a better way to do this?

 

[Frank Kabel]

Hi
use OFFSET. e.g.
=SUM(OFFSET($A$1:$A10,(ROW()-1)*10,0))
and copy this down

 

[Guest]

Thank you! This works great!

Hi
use OFFSET. e.g.
=SUM(OFFSET($A$1:$A10,(ROW()-1)*10,0))
and copy this down