Rolling Average:week number;

[SPenney]

I have a sheet that needs to calculate a 53 week rolling average. Column A has the week number; Column B has the data to be averaged.

I'm familiar with the offset function when charting and wonder if this can be replicated for calculating an average to match my chart.

[Biff]

Hi!

So, what do you want to do?

Average the last 53 entries in column B?

What if there aren't 53 entries?

Biff

"SPenney" <[email protected]> wrote in
message news:[email protected]...
>
> I have a sheet that needs to calculate a 53 week rolling average.
> Column A has the week number; Column B has the data to be averaged.
>
> I'm familiar with the offset function when charting and wonder if this
> can be replicated for calculating an average to match my chart.
>
>
> --
> SPenney
> ------------------------------------------------------------------------
> SPenney's Profile:
> http://www.excelforum.com/member.php...fo&userid=1079
> View this thread: http://www.excelforum.com/showthread...hreadid=510252
>

[SPenney]

I'm starting with 53 entries and will be adding from this point forward.

[Domenic]

Assuming that Column B, starting at B2, contains your data, try...

=AVERAGE(OFFSET(B2,MATCH(9.99999999999999E+307,B2:B65536)-53,0,53))

Hope this helps!

In article <[email protected]>,
SPenney <[email protected]> wrote:

> I have a sheet that needs to calculate a 53 week rolling average.
> Column A has the week number; Column B has the data to be averaged.
>
> I'm familiar with the offset function when charting and wonder if this
> can be replicated for calculating an average to match my chart.

[SPenney]

Domenic--

That appears to have done it! Thanks much.