sum quarters to years; predefined shift of ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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.
 
Back
Top