Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a rolling range of cells
How do I sum the adjacent 4 columns into a cell? I want an cell to always sum
the 4 cells to the left, allowing for me inserting a new column each week, ie. a rolling answer. Any help greatly appreciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a rolling range of cells
Use a mixed reference in your SUM function: =SUM($C2:F2), if columns C to F
are your 4 columns, always insert a new row before the last data column (i.e., your 3rd column with 4 columns of data, the 4th column with 5 columns of data etc.) and then you AutoFill the formula down to the remaining rows in the last column...I am assuming you are summing. The alternative is VBA where you make Excel look for a new column to the left of the SUM column and then programatically change the range argument of the SUM function in the 5th, 6th etc. column where you are summing. -- Gnothi se auton. "Mark" wrote: How do I sum the adjacent 4 columns into a cell? I want an cell to always sum the 4 cells to the left, allowing for me inserting a new column each week, ie. a rolling answer. Any help greatly appreciated. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a rolling range of cells
If the cell that contains the formula is E1, then one way:
=SUM(OFFSET(E1,0,-4,1,4)) Mark wrote: How do I sum the adjacent 4 columns into a cell? I want an cell to always sum the 4 cells to the left, allowing for me inserting a new column each week, ie. a rolling answer. Any help greatly appreciated. Thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a rolling range of cells
Offset assumes you know how many columns there are going to be. You will have
to change it every time you insert a column which at that point, you may as well reselect the SUM function range as it will be faster to do. -- Gnothi se auton. "Dave Peterson" wrote: If the cell that contains the formula is E1, then one way: =SUM(OFFSET(E1,0,-4,1,4)) Mark wrote: How do I sum the adjacent 4 columns into a cell? I want an cell to always sum the 4 cells to the left, allowing for me inserting a new column each week, ie. a rolling answer. Any help greatly appreciated. Thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a rolling range of cells
Consider this UDF:
Function zum() As Variant Application.Volatile With Application.Caller zum = .Offset(0, -4) + .Offset(0, -3) + .Offset(0, -2) + .Offset(0, -1) End With End Function It will always sum the four cells to the immediate left of the cell containing the function. -- Gary''s Student - gsnu200758 "Mark" wrote: How do I sum the adjacent 4 columns into a cell? I want an cell to always sum the 4 cells to the left, allowing for me inserting a new column each week, ie. a rolling answer. Any help greatly appreciated. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a rolling range of cells
The OP said that he wanted to always sum 4 columns to the left.
I don't understand your comment. xrelanon wrote: Offset assumes you know how many columns there are going to be. You will have to change it every time you insert a column which at that point, you may as well reselect the SUM function range as it will be faster to do. -- Gnothi se auton. "Dave Peterson" wrote: If the cell that contains the formula is E1, then one way: =SUM(OFFSET(E1,0,-4,1,4)) Mark wrote: How do I sum the adjacent 4 columns into a cell? I want an cell to always sum the 4 cells to the left, allowing for me inserting a new column each week, ie. a rolling answer. Any help greatly appreciated. Thanks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing periodic sales on a rolling basis | Excel Worksheet Functions | |||
Summing cells in a range that has #VALUE! | Excel Discussion (Misc queries) | |||
How to Calculate a sum between a rolling data range. | Excel Discussion (Misc queries) | |||
Z Chart i.e. top rolling annual bottom rolling monthly middle cum. | Excel Worksheet Functions | |||
summing part of cells in a range | Excel Discussion (Misc queries) |