sum quarters to years; predefined shift of ranges
 

Hi all,

Hopefully someone can help me solve this issue:
Suppose I have quarterly data for several years in a single row.
Then it is simple to sum the quarters of the first year to get a year's
result.

If I then copy this cell one position to the right, the result is Q2 + Q3 +
Q4 + Q1 of the next year.

Question: how do I copy a cell one position to the right, but at the same
time move 4 positions in the source row?
And the next cell: position + another 4, and again, and again, to get full
year's results.
Can this be done?

Cheers, Goose

sum quarters to years; predefined shift of ranges
 

You'll need to use the Offset() function
If your first SUM is in col A, and the first 4 Qrtrs are in A-D, next 4 in
E-H, etc, then use this formula in col A and copy it to the right:

=SUM(OFFSET($A$1,0,4*(COLUMN(A1)-1),1,4))

if your data starts in a different column, adjust the $A$1 reference to
reflect the left-most cell. NO NOT change the Column(A1) reference


"Goose" wrote:

Hi all,

Hopefully someone can help me solve this issue:
Suppose I have quarterly data for several years in a single row.
Then it is simple to sum the quarters of the first year to get a year's
result.

If I then copy this cell one position to the right, the result is Q2 + Q3 +
Q4 + Q1 of the next year.

Question: how do I copy a cell one position to the right, but at the same
time move 4 positions in the source row?
And the next cell: position + another 4, and again, and again, to get full
year's results.
Can this be done?

Cheers, Goose

sum quarters to years; predefined shift of ranges
 

=SUM(OFFSET($A1,0,4*(COLUMN()-COLUMN($A5)),1,4))
replacing $A1 by the reference to your first Q2 cell, and $A5 by the
reference to the cell where you are putting the formula for your first
year's total.
That should copy to the right appropriately.
--
David Biddulph

"Goose" wrote in message
...
Hi all,

Hopefully someone can help me solve this issue:
Suppose I have quarterly data for several years in a single row.
Then it is simple to sum the quarters of the first year to get a year's
result.

If I then copy this cell one position to the right, the result is Q2 + Q3
+
Q4 + Q1 of the next year.

Question: how do I copy a cell one position to the right, but at the same
time move 4 positions in the source row?
And the next cell: position + another 4, and again, and again, to get full
year's results.
Can this be done?

Cheers, Goose

sum quarters to years; predefined shift of ranges
 

Great Duke,

Thanks a lot, this saves me a lot of work and potential errors.

Cheers, Goose



"Duke Carey" wrote:

You'll need to use the Offset() function
If your first SUM is in col A, and the first 4 Qrtrs are in A-D, next 4 in
E-H, etc, then use this formula in col A and copy it to the right:

=SUM(OFFSET($A$1,0,4*(COLUMN(A1)-1),1,4))

if your data starts in a different column, adjust the $A$1 reference to
reflect the left-most cell. NO NOT change the Column(A1) reference


"Goose" wrote:

Hi all,

Hopefully someone can help me solve this issue:
Suppose I have quarterly data for several years in a single row.
Then it is simple to sum the quarters of the first year to get a year's
result.

If I then copy this cell one position to the right, the result is Q2 + Q3 +
Q4 + Q1 of the next year.

Question: how do I copy a cell one position to the right, but at the same
time move 4 positions in the source row?
And the next cell: position + another 4, and again, and again, to get full
year's results.
Can this be done?

Cheers, Goose

sum quarters to years; predefined shift of ranges
 

Thanks David for your help. Greatly appreciated. Goose

"David Biddulph" wrote:

=SUM(OFFSET($A1,0,4*(COLUMN()-COLUMN($A5)),1,4))
replacing $A1 by the reference to your first Q2 cell, and $A5 by the
reference to the cell where you are putting the formula for your first
year's total.
That should copy to the right appropriately.
--
David Biddulph

"Goose" wrote in message
...
Hi all,

Hopefully someone can help me solve this issue:
Suppose I have quarterly data for several years in a single row.
Then it is simple to sum the quarters of the first year to get a year's
result.

If I then copy this cell one position to the right, the result is Q2 + Q3
+
Q4 + Q1 of the next year.

Question: how do I copy a cell one position to the right, but at the same
time move 4 positions in the source row?
And the next cell: position + another 4, and again, and again, to get full
year's results.
Can this be done?

Cheers, Goose