Formula problems: weekly totals and offset function

[Terry Regan]

Hi everyone,

I have two columns of data (A and B) that go on for several thousand
rows. This is what my boss calls day to day data and he has asked me
to calculate the weekly totals and averages for him.

I can do the first one longhand

C2=SUM(A2:A8)/SUM(B2:B8)

But it's when I do the second and subsequent ones that I have a
problem. I could do them all longhand by adding 7 to the A and B
references but is there a quicker and more accurate way?

I've tried the offset command but this doesn't seem to allow me to do
OFFSET(SUM(...)) - can anyone help me out or tell me where I'm going
wrong with the switches and syntax? Thanks,

Terry

 

[dave]

ok. I think I've figured out what your trying to do.
continuing with your reference example, add the following
to cell c2 and d2, and then copy down. Before adding
these formulas, make sure that you have a blank row on top
so that cell d1 = 0. column c should give you your
answers for every 7 days, and 0s everywhere else.

c2: =IF(D2=7,SUM(OFFSET(B2,-6,-1,1,1):A2)/SUM(OFFSET(B2,-
6,0,1,1):B2),0)

d2:=IF(D1=7,1,D1+1)

Your try at the offset function was the right idea, but
the sum has to be outside the offset function.

hth,
Dave

 

[Bob Phillips]

Terry,

Try

=SUM(A1:OFFSET(A1,COUNTA(A:A)-1,0))/SUM(B1:OFFSET(B1,COUNTA(B:B)-1,0))