How to find avg for step size?

O

Omkar

How to find avg for specific step size? I have annual data and want to find
weekly avg. i.e avg A1:A6 then A7:A14 etc.. How to do it using sigle formula?

Thank you.
 
M

Max

Omkar said:
How to find avg for specific step size? I have annual data and want to find
weekly avg. i.e avg A1:A6 then A7:A14 etc.. How to do it using sigle formula?
Click to expand...

One way ..

Source data is assumed in A1 down

Put in any starting cell, say in B2:
=AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,6))
Copy B2 down

B2 returns: =AVERAGE(A1:A6)
B3 returns: =AVERAGE(A7:A14)
and so on ..
 
M

Max

Omkar said:
How to find avg for specific step size? I have annual data and want to find
weekly avg. i.e avg A1:A6 then A7:A14 etc.. How to do it using sigle formula?
Click to expand...

One way ..

Source data is assumed in A1 down

Put in any starting cell, say in B2:
=AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,6))
Copy B2 down

B2 returns: =AVERAGE(A1:A6)
B3 returns: =AVERAGE(A7:A14)
and so on ..
 
M

Max

Corrections, sorry:
Put in any starting cell, say in B2:
=AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,6))
Click to expand...

The formula in B2 should be:
=AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,7))

and lines
B2 returns: =AVERAGE(A1:A6)
B3 returns: =AVERAGE(A7:A14)
and so on ..
Click to expand...

should read as:
 
O

Omkar

Thank you Max.

I did as you said, but I am getting different answers. (I compared values
which I found from separating each data series of 7 points and finding their
avgs.)
Can you explain what is significance of each term. I know some. But you
have written nothing for Rows,Columns,Height for Offset function.
Also what is *7-6 signifies in reference?

Please try to help me.
Thank you for your help.
 
B

Biff

Hi!

What is the starting cell of the range where your numbers are located? Is it
A1?

What step size do you want? In your post you have: A1:A6 then A7:A14 etc..

A1 to A6 = 6
A7 to A14 = 8

Biff
 
M

Max

Omkar said:
I did as you said, but I am getting different answers. (I compared values
which I found from separating each data series of 7 points and finding their
avgs.)
Click to expand...

I'm not sure what happened over there,
but here's a sample implementation to illustrate ..
http://www.savefile.com/files/6466382
Averaging 7 cell column ranges.xls

Btw, I had presumed there were some typos in your original post's range refs:
.. weekly avg. i.e avg A1:A6 then A7:A14 ..
Click to expand...
I focused more on your "weekly avg" and presumed it should have read as:
avg A1:A7 then A8:A14 ..
Can you explain what is significance of each term. I know some. But you
have written nothing for Rows,Columns,Height for Offset function.
Also what is *7-6 signifies in reference?
Click to expand...

Some explanations ..

In the expression: OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,7)

ROW(A1) is used as the serial incrementer when we copy down
In any cell, =ROW(A1) resolves to 1, when copied down, it becomes ROW(A2)
which returns 2, then ROW(A3) which returns 3 and so on..

The *7-6 is an arithmetic op applied so that we can get the desired series:
1, 8, 15, ... (steps of 7 cells), viz.:

ROW(A1)*7-6 resolves to: 1 x 7 - 6 = 1
ROW(A2)*7-6 gives: 2 x 7 - 6 = 8
ROW(A3)*7-6 returns: 3 x 7 - 6 = 15
and so on, as the formula is copied down
(see the above results by putting in any cell: =ROW(A1)*7-6, then copy down)

The numbers 1, 8, 15 are then concatenated with "A" to yield the text string
cell refs: A1, A8, A15 which represent the desired start points for the
ranges:

A1:A7
A8:A14
A15:A22

INDIRECT resolves the text string cell refs to give the OFFSET references,
viz.:

OFFSET(A1,,,7)
OFFSET(A8,,,7)
OFFSET(A15,,,7)

The "7" is the height param in OFFSET which grabs a 7 cell col range from
the OFFSET reference, hence the 3 OFFSETs above would effectively return the
ranges:

A1:A7
A8:A14
A15:A22

for the AVERAGE( ... ) function to evaluate
 
Top