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

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?

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

--
Regards
Frank Kabel
Frankfurt, Germany

picklet222 wrote:
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?

Thank you! This works great!

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

picklet222 wrote:
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?