Making the formula Dynamic based on Date Range

[apie5]

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=&quot]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=&quot][/FONT][TABLE="width: 721"]
<colgroup><col><col span="7"><col><col></colgroup><tbody>[TR]
[TD="align: right"]12/30/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.024291667[/TD]
[TD="align: right"]0.018987[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.022041667[/TD]
[TD="align: right"]0.018284[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.046791667[/TD]
[TD="align: right"]0.038542[/TD]
[/TR]
[TR]
[TD="align: right"]3/31/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.001675[/TD]
[TD="align: right"]0.000375[/TD]
[/TR]
[TR]
[TD="align: right"]4/28/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.016591667[/TD]
[TD="align: right"]0.009308[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.01625[/TD]
[TD="align: right"]0.012773[/TD]
[/TR]
[TR]
[TD="align: right"]6/30/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.007408333[/TD]
[TD="align: right"]0.005105[/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.021316667[/TD]
[TD="align: right"]0.019516[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.008683333[/TD]
[TD="align: right"]0.00149[/TD]
[/TR]
[TR]
[TD="align: right"]9/29/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.015625[/TD]
[TD="align: right"]0.019455[/TD]
[/TR]
[TR]
[TD="align: right"]10/31/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.022016667[/TD]
[TD="align: right"]0.022207[/TD]
[/TR]
[TR]
[TD="align: right"]11/30/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.030373333[/TD]
[TD="align: right"]0.029077[/TD]
[/TR]
[TR]
[TD="align: right"]12/29/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.010536667[/TD]
[TD="align: right"]0.009798[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.064368333[/TD]
[TD="align: right"]0.05589[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.048175[/TD]
[TD="align: right"]-0.03866[/TD]
[/TR]
[TR]
[TD="align: right"]3/29/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.029423333[/TD]
[TD="align: right"]-0.02709[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.002996667[/TD]
[TD="align: right"]0.002169[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.030476667[/TD]
[TD="align: right"]0.022007[/TD]
[/TR]
[TR]
[TD="align: right"]6/29/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.011088333[/TD]
[TD="align: right"]0.004318[/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.039238333[/TD]
[TD="align: right"]0.035414[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.032088333[/TD]
[TD="align: right"]0.030296[/TD]
[/TR]
[TR]
[TD="align: right"]9/28/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.005398333[/TD]
[TD="align: right"]0.003681[/TD]
[/TR]
[TR]
[TD="align: right"]10/31/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.071401667[/TD]
[TD="align: right"]-0.07041[/TD]
[/TR]
[TR]
[TD="align: right"]11/30/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.02[/TD]
[TD="align: right"]0.017823[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.086621667[/TD]
[TD="align: right"]-0.09263[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.09234[/TD]
[TD="align: right"]0.077703[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.032046667[/TD]
[TD="align: right"]0.02941[/TD]
[/TR]
[TR]
[TD="align: right"]3/29/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.01903[/TD]
[TD="align: right"]0.016978[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.037898333[/TD]
[TD="align: right"]0.038147[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.069383333[/TD]
[TD="align: right"]-0.0662[/TD]
[/TR]
[TR]
[TD="align: right"]6/28/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.065645[/TD]
[TD="align: right"]0.068085[/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.015076667[/TD]
[TD="align: right"]0.012217[/TD]
[/TR]
[TR]
[TD="align: right"]8/30/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.024263333[/TD]
[TD="align: right"]-0.01815[/TD]
[/TR]
[TR]
[TD="align: right"]9/30/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.016253333[/TD]
[TD="align: right"]0.016534[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Possibly:

=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:

=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

 

[apie5]

Possibly:

=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:

=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!

 

[Eric W]

Happy to help!

 

[apie5]

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))

 

[Eric W]

Sure, same idea:

=(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))))

 

[apie5]

Sure, same idea:

=(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!