How to find avg for step size?
 

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.

How to find avg for step size?
 

"Omkar" wrote:
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?

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How to find avg for step size?
 

"Omkar" wrote:
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?

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How to find avg for step size?
 

Corrections, sorry:

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

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

should read as:
B2 returns: =AVERAGE(A1:A7)
B3 returns: =AVERAGE(A8:A14)
and so on ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How to find avg for step size?
 

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.


"Max" wrote:

Corrections, sorry:

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

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

should read as:
B2 returns: =AVERAGE(A1:A7)
B3 returns: =AVERAGE(A8:A14)
and so on ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How to find avg for step size?
 

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

"Omkar" wrote in message
...
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.


"Max" wrote:

Corrections, sorry:

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

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

should read as:
B2 returns: =AVERAGE(A1:A7)
B3 returns: =AVERAGE(A8:A14)
and so on ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How to find avg for step size?
 

"Omkar" wrote:
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.)

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 ..
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?

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How to find avg for step size?
 

Typo, lines: A15:A22
should read as: A15:A21

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---