View original (French)

Excel: rolling 12-month chart

tuany Posted messages 19 Status Member -  
G.David Posted messages 790 Status Member -
I must create a graph for a rolling 12-month period, meaning that we are in March, so the data should go from April 2004 to March 2005, and next month from May 2004 to April 2005, etc...
Help me :)
Thank you!!!

3 answers

Answer 1 / 3
Armojax Posted messages 1863 Registration date   Status Member Last intervention   1 529
 
Hello tuany,

Just a question: do your months really slide (1st example below: we add a new column every month), or do you always rotate on the same 12 months (March 2005 takes the place of March 2004)?
 Month 02 03 04 05 06 07 08 09 10 11 12 |01 02 03 Data 5 6 7 2 5 6 1 3 5 7 2 | 6 3 1 <----------------------------------|---------> 2004 | 2005 Or Month 01 02 03 04 05 06 07 08 09 10 11 12 Data 6 3 1 7 2 5 6 1 3 5 7 2 <--------> <--------------------------------> 2005 2004
0
legege31 Posted messages 4 Status Member
 
I am facing the same problem but with configuration number 2

Month 01 02 03 04 05 06 07 08 09 10 11 12
Data 6 3 1 7 2 5 6 1 3 5 7 2
<--------------------------------><---------------------->
2008 2007

Thank you in advance
legege31
0
G.David Posted messages 790 Status Member 203 > legege31 Posted messages 4 Status Member
 
You can look at the help with the function " month.offset( end.of.month(today())+1 ;-1)
which should give you the date of the first of the current month to place in the twelfth column
in the eleventh column month.offset(cell in column 12;-1) to pull to the left
below a vlookup(cell above;table;2,false) to pull under all your month row
for your table
in insert name
table = offset (top left corner;0;0;counta(left column);2)
which should make the table dynamic
i cannot be more precise i am working without excel at hand
--
respect is not a given
it is earned
http://creationdavid.art-ifice.fr
Best regards
G.David
1