calculte 12 months based on forecast quarter values

[ramakriishna]

My Fore cast file contains six quurter columns based on these six columns i have to crete

12 month columns.these month columns are dynamic example if this month is march

12 columns are march-2013,apr-2013,-----------------,feb-2014

Final forecast file looks like this

rowno,q1,q2,q3,q4,q5,q6,Mar-13,apr-13,-----------------,feb-2014

1 2, 2 , 3, 4, 5,7 ,q1/3,q2/3,----------------------,q5/3

If it is march. march falls on first quarter so march value is q1/3

apr,may jun,falls on sencond quarter so apr,may,june value is q2/3

feb-2014,falla on q4 quarte so feb-2014 value is q4/3


For next month i.e apr falls on quarter two now my apr-jun quarter gets value from q1

jul-sep gets value from q2,oct-dec gets value from q3,jan-14 to mar-14 gets values from q4

apr month forecast

rowno,q1,q2,q3,q4,q5,q6,apr-13,may-13,--------------,mar-14

1 , 2,2, 2, 2, 2, 2, q1/3,q1/3,-----------------,q4/3

may month forecast file

rowno,q1,q2,q3,q4,q5,q6,may-13,---------------------,apr-14

1, 2,2, 2 ,2, 2, 2, q1/3,-----------------------,q5/3


For Third Quarter

jul-sep gets values from q1,oct-dec gets value from q2,jan14-mar14 gets from q3,apr14-jun14 gets value from q4

 

[Hui]

Ramakriishna


Can you upload a sample file with some data

It might help make the problem clearer.


Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[ramakriishna]

My Fore cast file contains six quurter columns based on these six columns i have to crete

12 month columns.these month columns are dynamic example if this month is march

12 columns are march-2013,apr-2013,-----------------,feb-2014

Final forecast file looks like this

rowno,q1,q2,q3,q4,q5,q6,Mar-13,apr-13,-----------------,feb-2014

1 2, 2 , 3, 4, 5,7 ,q1/3,q2/3,----------------------,q5/3

If it is march. march falls on first quarter so march value is q1/3

apr,may jun,falls on sencond quarter so apr,may,june value is q2/3

feb-2014,falla on q4 quarte so feb-2014 value is q4/3


For next month i.e apr falls on quarter two now my apr-jun quarter gets value from q1

jul-sep gets value from q2,oct-dec gets value from q3,jan-14 to mar-14 gets values from q4

apr month forecast

rowno,q1,q2,q3,q4,q5,q6,apr-13,may-13,--------------,mar-14

1 , 2,2, 2, 2, 2, 2, q1/3,q1/3,-----------------,q4/3

may month forecast file

rowno,q1,q2,q3,q4,q5,q6,may-13,---------------------,apr-14

1, 2,2, 2 ,2, 2, 2, q1/3,-----------------------,q5/3


For Third Quarter

jul-sep gets values from q1,oct-dec gets value from q2,jan14-mar14 gets from q3,apr14-jun14 gets value from q4

http://www.2shared.com/file/djk_JXqy/forecast.html

 

[Hui]

Ramakriishna


In J2: =OFFSET($B2,,INT((MONTH(J$1)+2)/3)+(YEAR(J$1)>2013)*4)

Copy right and down

 

[ramakriishna]

Thank You Ninja But my forecast file changes for every quater

Than q1 becomes apr- may

q2 becomes jul-sep

q3 becomes oct-dec

q4 becomes jan-14-mar-14

q4 becomes apr-14-jun-14

For next quarte the new i.e for 3rd quarter

q1 --> jul-sep

q2 --> oct-dec

q3 --> jan-14 -mar -14

q4 --> apr-14 - jun14