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
[FONT="]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?
[/FONT]thanks!
[FONT="][/FONT]
<colgroup><col><col span="7"><col><col></colgroup><tbody>
</tbody>
I have this formula : =INDEX(LINEST(I62:I73,J62:J73),2)*12
[FONT="]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?
[/FONT]thanks!
[FONT="][/FONT]
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 |
<colgroup><col><col span="7"><col><col></colgroup><tbody>
</tbody>