Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
picklet222
 
Posts: n/a
Default 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?
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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?



  #3   Report Post  
picklet222
 
Posts: n/a
Default

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?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to make Excel run limited number of formulas on a given worksh John Excel Discussion (Misc queries) 0 January 12th 05 05:29 PM
Way to make Excel only run certain formulas on a worksheet? jrusso Excel Discussion (Misc queries) 0 January 12th 05 05:23 PM
fill formulas and values Jose Mourinho Excel Discussion (Misc queries) 1 January 4th 05 04:05 PM
Identifying the Active Fill Color Steve Conary Excel Discussion (Misc queries) 3 December 9th 04 05:45 AM
calculating formulas for all workbooks in a folder Chad Excel Worksheet Functions 3 November 13th 04 06:22 PM


All times are GMT +1. The time now is 09:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"