Results 1 to 5 of 5

Rolling 6 & 12 month variance (dynamic formula)

This is a discussion on Rolling 6 & 12 month variance (dynamic formula) within the Excel Questions forums, part of the Question Forums category; I have this table of data that will grow as time passes (new months data will be added) ******** ******************** ...

  1. #1
    Board Regular
    Join Date
    Dec 2002
    Location
    Costa Rica
    Posts
    522

    Default Rolling 6 & 12 month variance (dynamic formula)

    I have this table of data that will grow as time passes (new months data will be added)
    ******** ******************** ************************************************************************>
    Microsoft Excel - Fiscal Forecasting Template v2.2mikesupdate.xlsx___Running: 12.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    C
    D
    E
    F
    G
    H
    1
    Average20052006200720082009
    2
    46,17741,99352,76747,66242,28435403
    3
    46,72440,65753,47049,20543,56536672
    4
    45,60841,28752,91546,77541,45434801
    5
    44,54840,71650,66945,05241,7550
    6
    42,24839,78649,31441,21638,6760
    7
    43,12638,63049,98943,67540,2090
    8
    41,28539,42147,69240,10437,9210
    9
    48,36353,97951,58745,92541,9590
    10
    48,82654,04951,29547,60542,3550
    11
    48,47656,05550,40045,38042,0700
    12
    42,43150,25443,41539,26936,7860
    13
    39,99748,85341,84036,28433,0090
    Monthly

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.




    I have this formulas to give me a rolling 6 and 12 month variance what I have to do now if update them by hand as new data is added anyone know of a way to make them Dynamic so as new data is added the old one drops off and the new data takes it place

    I am open to a formula or VBA

    Thanks in advance


    ******** ******************** ************************************************************************>
    Microsoft Excel - Fiscal Forecasting Template v2.2mikesupdate.xlsx___Running: 12.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    B
    C
    D
    E
    31
    Rolling 6 Month Growth-4.88%
    32
    Rolling 12 Month Growth-9.19%
    Monthly

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    We are living in a world today
    where lemonade is made from
    artificial flavoring and furniture polish
    is made from real lemons...
    Alfred E Newman

  2. #2
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,723

    Default Re: Rolling 6 & 12 month variance (dynamic formula)

    Since you have data until Mar 2009 why does your analysis only extend to Aug 2008?

    Also, a dynamic formula would be a lot easier if your data were laid out in a single table with year in one column, month in the next, and the amount in a third.

  3. #3
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,318

    Default Re: Rolling 6 & 12 month variance (dynamic formula)

    try this
    enter the month (Aug-08) , from which you want to look back, in a cell

    say D31 here

    for 6 months

    Code:
     
    =(SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=$D$31),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-5,1)),$D$2:$H$13)-
    SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=DATE(YEAR($D$31),MONTH($D$31)-6,1)),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-11,1)),$D$2:$H$13))/
    SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=DATE(YEAR($D$31),MONTH($D$31)-6,1)),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-11,1)),$D$2:$H$13)
    for 12 months

    Code:
     
    =(SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=$D$31),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-11,1)),$D$2:$H$13)-
    SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=DATE(YEAR($D$31),MONTH($D$31)-12,1)),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-23,1)),$D$2:$H$13))/
    SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=DATE(YEAR($D$31),MONTH($D$31)-12,1)),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-23,1)),$D$2:$H$13)
    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  4. #4
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,318

    Default Re: Rolling 6 & 12 month variance (dynamic formula)

    forgot to mention,

    you need to have month names as row titles, for this formula to work

    B
    C
    D
    E
    F
    G
    H
    1
    Average
    2005
    2
    Jan
    46177
    41993
    52767
    47662
    42284
    35403
    3
    Feb
    46724
    40657
    53470
    49205
    43565
    36672
    4
    Mar
    45608
    41287
    52915
    46775
    41454
    34801
    5
    Apr
    44548
    40716
    50669
    45052
    41755
    0
    6
    May
    42248
    39786
    49314
    41216
    38676
    0
    7
    Jun
    43126
    38630
    49989
    43675
    40209
    0
    8
    Jul
    41285
    39421
    47692
    40104
    37921
    0
    9
    Aug
    48363
    53979
    51587
    45925
    41959
    0
    10
    Sep
    48826
    54049
    51295
    47605
    42355
    0
    11
    Oct
    48476
    56055
    50400
    45380
    42070
    0
    12
    Nov
    42431
    50254
    43415
    39269
    36786
    0
    13
    Dec
    39997
    48853
    41840
    36284
    33009
    0
    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  5. #5
    Board Regular
    Join Date
    Dec 2002
    Location
    Costa Rica
    Posts
    522

    Default Re: Rolling 6 & 12 month variance (dynamic formula)

    Thanks
    We are living in a world today
    where lemonade is made from
    artificial flavoring and furniture polish
    is made from real lemons...
    Alfred E Newman

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com