Rolling Average

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

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!

 

[SPenney]

Domenic--

That appears to have done it! Thanks much.

 

[SPenney]

Domenic--

That appears to have done it! Thanks much.