How to maintain a set range of cells when dragging a formula (e.gmonthlyannual data)
 

If I have a formula such as SUM(A1:A12) and drag/copy it one cell down
it becomes SUM(A2:A13), but what if I only want to use each cell once,
say to convert monthly data to annual data. so that dragging the
formula down would give:

SUM(A13:A24) then
SUM(A25:A36)
SUM(A37:A48) etc.

How to maintain a set range of cells when dragging a formula (e.gmonthlyannual data)
 

Put this in your first cell:

=SUM(INDIRECT("A"&ROW(A1)*12+1&":A"&(ROW(A1)+1)*12 ))

then copy down as required.

Hope this helps.

Pete

On Feb 3, 11:08*am, Justinalexander wrote:
If I have a formula such as SUM(A1:A12) and drag/copy it one cell down
it becomes SUM(A2:A13), but what if I only want to use each cell once,
say to convert monthly data to annual data. so that dragging the
formula down would give:

SUM(A13:A24) then
SUM(A25:A36)
SUM(A37:A48) etc.

How to maintain a set range of cells when dragging a formula (e.g
 

If your new data for new month, is beneath the present month, then dragging
the sum for present will populate the data for next month.

--
R. Khoshravan
Please click "Yes" if it is helpful.


"Justinalexander" wrote:

If I have a formula such as SUM(A1:A12) and drag/copy it one cell down
it becomes SUM(A2:A13), but what if I only want to use each cell once,
say to convert monthly data to annual data. so that dragging the
formula down would give:

SUM(A13:A24) then
SUM(A25:A36)
SUM(A37:A48) etc.