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

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

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(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

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