Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
fill formulas and values | Excel Discussion (Misc queries) | |||
Identifying the Active Fill Color | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |