Results 1 to 7 of 7

Thread: Making the formula Dynamic based on Date Range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Making the formula Dynamic based on Date Range

    Hi All -- I was hoping you can help me make the following formula dynamic based on a date range..

    I have this formula : =INDEX(LINEST(I62:I73,J62:J73),2)*12

    I have a table of dates (end of month dates) and two columns with numbers which I need summed up based on the most recent 12 months. The date set dates back to 2014 and goes all the way to 9/30/2019. I need to be able to be calculate the data from 10/31/2018 - 9/30/2019.. I want my formula to be dynamic, so next month I would like to have the data set calculate the months of 11/30/2018 - 10/31/2019. Here is a sample set of my data table, data was removed for columns that are irrelevant. Is there a way to make the above formula dynamic based on the most recent month end going back 12 months?

    thanks!

    12/30/2016 0 0 0 0 0 0 0 0.024291667 0.018987
    1/31/2017 0 0 0 0 0 0 0 0.022041667 0.018284
    2/28/2017 0 0 0 0 0 0 0 0.046791667 0.038542
    3/31/2017 0 0 0 0 0 0 0 0.001675 0.000375
    4/28/2017 0 0 0 0 0 0 0 0.016591667 0.009308
    5/31/2017 0 0 0 0 0 0 0 0.01625 0.012773
    6/30/2017 0 0 0 0 0 0 0 -0.007408333 0.005105
    7/31/2017 0 0 0 0 0 0 0 0.021316667 0.019516
    8/31/2017 0 0 0 0 0 0 0 0.008683333 0.00149
    9/29/2017 0 0 0 0 0 0 0 0.015625 0.019455
    10/31/2017 0 0 0 0 0 0 0 0.022016667 0.022207
    11/30/2017 0 0 0 0 0 0 0 0.030373333 0.029077
    12/29/2017 0 0 0 0 0 0 0 0.010536667 0.009798
    1/31/2018 0 0 0 0 0 0 0 0.064368333 0.05589
    2/28/2018 0 0 0 0 0 0 0 -0.048175 -0.03866
    3/29/2018 0 0 0 0 0 0 0 -0.029423333 -0.02709
    4/30/2018 0 0 0 0 0 0 0 0.002996667 0.002169
    5/31/2018 0 0 0 0 0 0 0 0.030476667 0.022007
    6/29/2018 0 0 0 0 0 0 0 0.011088333 0.004318
    7/31/2018 0 0 0 0 0 0 0 0.039238333 0.035414
    8/31/2018 0 0 0 0 0 0 0 0.032088333 0.030296
    9/28/2018 0 0 0 0 0 0 0 0.005398333 0.003681
    10/31/2018 0 0 0 0 0 0 0 -0.071401667 -0.07041
    11/30/2018 0 0 0 0 0 0 0 0.02 0.017823
    12/31/2018 0 0 0 0 0 0 0 -0.086621667 -0.09263
    1/31/2019 0 0 0 0 0 0 0 0.09234 0.077703
    2/28/2019 0 0 0 0 0 0 0 0.032046667 0.02941
    3/29/2019 0 0 0 0 0 0 0 0.01903 0.016978
    4/30/2019 0 0 0 0 0 0 0 0.037898333 0.038147
    5/31/2019 0 0 0 0 0 0 0 -0.069383333 -0.0662
    6/28/2019 0 0 0 0 0 0 0 0.065645 0.068085
    7/31/2019 0 0 0 0 0 0 0 0.015076667 0.012217
    8/30/2019 0 0 0 0 0 0 0 -0.024263333 -0.01815
    9/30/2019 0 0 0 0 0 0 0 0.016253333 0.016534

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,714
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Making the formula Dynamic based on Date Range

    Possibly:

    Code:
    =INDEX(LINEST(OFFSET(I1:I12,MATCH(EOMONTH(TODAY(),-12),A1:A1000,0)-1,0),OFFSET(J1:J12,MATCH(EOMONTH(TODAY(),-12),A1:A1000,0)-1,0)),2)*12
    Longer, but non-volatile:

    Code:
    =INDEX(LINEST(INDEX(I:I,MATCH(EOMONTH(TODAY(),-12),A1:A1000,0)):INDEX(I:I,MATCH(EOMONTH(TODAY(),-1),A1:A1000,0)),INDEX(J:J,MATCH(EOMONTH(TODAY(),-12),A1:A1000,0)):INDEX(J:J,MATCH(EOMONTH(TODAY(),-1),A1:A1000,0))),2)*12
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making the formula Dynamic based on Date Range

    Quote Originally Posted by Eric W View Post
    Possibly:

    Code:
    =INDEX(LINEST(OFFSET(I1:I12,MATCH(EOMONTH(TODAY(),-12),A1:A1000,0)-1,0),OFFSET(J1:J12,MATCH(EOMONTH(TODAY(),-12),A1:A1000,0)-1,0)),2)*12
    Longer, but non-volatile:

    Code:
    =INDEX(LINEST(INDEX(I:I,MATCH(EOMONTH(TODAY(),-12),A1:A1000,0)):INDEX(I:I,MATCH(EOMONTH(TODAY(),-1),A1:A1000,0)),INDEX(J:J,MATCH(EOMONTH(TODAY(),-12),A1:A1000,0)):INDEX(J:J,MATCH(EOMONTH(TODAY(),-1),A1:A1000,0))),2)*12

    I used the second one and it worked like a charm!! Thank you so much!

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,714
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Making the formula Dynamic based on Date Range

    Happy to help!

  5. #5
    New Member
    Join Date
    Jun 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making the formula Dynamic based on Date Range

    Hi Eric -- Is there a way to make this formula do the same? Same deal as above. I want to calculate dynamically based on the most recent date and go back twelve months.. thanks!

    =(12*AVERAGE(I62:I7300))/(SQRT(12)*STDEV.S(I62:I7300))

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,714
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Making the formula Dynamic based on Date Range

    Sure, same idea:

    Code:
    =(12*AVERAGE(INDEX(I:I,MATCH(EOMONTH(TODAY(),-12),A1:A7300,0)):INDEX(I:I,MATCH(EOMONTH(TODAY(),-1),A1:A7300,0))))/(SQRT(12)*STDEV.S(INDEX(I:I,MATCH(EOMONTH(TODAY(),-12),A1:A7300,0)):INDEX(I:I,MATCH(EOMONTH(TODAY(),-1),A1:A7300,0))))
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    New Member
    Join Date
    Jun 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making the formula Dynamic based on Date Range

    Quote Originally Posted by Eric W View Post
    Sure, same idea:

    Code:
    =(12*AVERAGE(INDEX(I:I,MATCH(EOMONTH(TODAY(),-12),A1:A7300,0)):INDEX(I:I,MATCH(EOMONTH(TODAY(),-1),A1:A7300,0))))/(SQRT(12)*STDEV.S(INDEX(I:I,MATCH(EOMONTH(TODAY(),-12),A1:A7300,0)):INDEX(I:I,MATCH(EOMONTH(TODAY(),-1),A1:A7300,0))))

    Thank you so much!!! Have a wonderful weekend!

Some videos you may like

User Tag List

Tags for this Thread

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
  •