How to find avg for step size?

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

[Max]

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

[Max]

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

[Max]

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

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


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

[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

"Omkar" <[email protected]> wrote in message
news:[email protected]...
> 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
>> ---

[Max]

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

[Max]

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

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