Hi.
I need to be able to calculate a five year rolling standard deviation based on profitabilities for a number of companies for a period of 10 years.
My data looks something like this:
Comp Year Profitability
A 2000 0.145
A 2001 0.155
A 2002 0.124
A 2003 0.156
A 2004 0.356
A 2005 0.356
A 2006 0.25
A 2007 0.1866
A 2008 0.58
A 2009 0.635
A 2010 0.02
B 2000 0.25
B 2001 0.287
B 2002 0.185
B 2003 0.82
B 2004 0.86
B 2005 0.41
Currently I am able to calculate it, but only for one company at a time:
For example for company a D5=std(C1:c5)
and then I drag it down upto D10.
If I were to drag it down all the way for all companies, for company B for example, it would be using the previous 4 data points of company A, so I have been doing them individually for each company.
Is there a way to calculate the 5 year moving standard quickly sorted for each company?
Thanks
Bookmarks