Rolling Average
 

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

Rolling Average
 

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

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

Rolling Average
 

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


--
SPenney
------------------------------------------------------------------------
SPenney's Profile: http://www.excelforum.com/member.php...fo&userid=1079
View this thread: http://www.excelforum.com/showthread...hreadid=510252

Rolling Average
 

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

Rolling Average
 

Domenic--

That appears to have done it! Thanks much.


--
SPenney
------------------------------------------------------------------------
SPenney's Profile: http://www.excelforum.com/member.php...fo&userid=1079
View this thread: http://www.excelforum.com/showthread...hreadid=510252

Rolling Average
 

Domenic--

That appears to have done it! Thanks much.


--
SPenney
------------------------------------------------------------------------
SPenney's Profile: http://www.excelforum.com/member.php...fo&userid=1079
View this thread: http://www.excelforum.com/showthread...hreadid=510252