Qtr results to annual results

[Matt Thomas]

Hi all,
I have a row with Qtr results for 8 years,
Y1Qtr1 Y1Qtr2 Y1Qtr3 Y1Qtr4 Y2Qtr1 etc

On another worksheet, i wish to aggregate these to annual
results.

Y1 Y2 Y3 etc
Is there a nested function that will allow me to sum the
relevant Qtr results without introducing another row of
reference numbers for use with SUMIF or OFFSET.
e.g. =SUM(OFFSET((OFFSET($C$4,0,D5,1,1)),0,0,1,4)) where
D5 sets the offset start location.

Cheers

Matt

 

[Mark Graesser]

Hi Matt
This might be what your looking for

=SUM(OFFSET((OFFSET($C$4,0,(D5-1)*4+1,1,4)),0,0,1,4)

D5 would contain the year (1,2,3...). (D5-1)*4+1 will then calculate the vertical offset based on that year to get to the first quarter. The last 4 in the OFFSET will collect all four quarters

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Matt Thomas wrote: ----

Hi all
I have a row with Qtr results for 8 years
Y1Qtr1 Y1Qtr2 Y1Qtr3 Y1Qtr4 Y2Qtr1 et

On another worksheet, i wish to aggregate these to annual
results

Y1 Y2 Y3 et
Is there a nested function that will allow me to sum the
relevant Qtr results without introducing another row of
reference numbers for use with SUMIF or OFFSET
e.g. =SUM(OFFSET((OFFSET($C$4,0,D5,1,1)),0,0,1,4)) where
D5 sets the offset start location

Cheer

Mat