Dynamic calculation for LTM/TTM (last 12 months)

[krodriguez]

Hello,

Is there a way to have a dynamic calculation for LTM...I want to get rid off =SUM(Cell1, Cell2, Cell3...Cell12) and manually change the cells once I move from month to month. Ideally the formula can detect the dates and just bring the values.

Example I have:
If I do LTM Dec-15 my results = $1200, what If I want to do LTM Jan-16 = $1300...the idea is to have a dynamic formula that will allow me to select my date, example Jan-16 and automatically calculate the LTM (so I don't mess with the formula), is this possible? Thanks



[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Jan-15[/TD]
[TD]Feb-15[/TD]
[TD]Mar-15[/TD]
[TD]Apr-15[/TD]
[TD]May-15[/TD]
[TD]Jun-15[/TD]
[TD]Jul-15[/TD]
[TD]Aug-15[/TD]
[TD]Sep-15[/TD]
[TD]Oct-15[/TD]
[TD]Nov-15[/TD]
[TD]Dec-15[/TD]
[TD]Jan-16[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Calculating with dates is a pain in Excel. But I think this might do it for your project. [N.B. Dates are entered here as Excel date serial numbers.]

{Having trouble with the HTML Editor...}
<b55) (b55<="$B$1))
<b55)*b66*(b55<=$b$1))[ code]
<b55)*b66*(b55<=$b$1))<b55)*b66*(b55<=$b$1))}
</b55)*b66*(b55<=$b$1))<b55)*b66*(b55<=$b$1))}
</b55)*b66*(b55<=$b$1))[></b55)>

 

[DRSteele]

This is annoying!

 

[DRSteele]

Try again:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	criteria	Nov 1, 2015
2	prior months	12
3	Total	1,100
4
5	Date	Jan 1, 2015	Feb 1, 2015	Mar 1, 2015	Apr 1, 2015	May 1, 2015	Jun 1, 2015	Jul 1, 2015	Aug 1, 2015	Sep 1, 2015	Oct 1, 2015	Nov 1, 2015	Dec 1, 2015	Jan 1, 2016	Feb 1, 2016	Mar 1, 2016
6	Revenue	100	100	100	100	100	100	100	100	100	100	100	100	200	500	900
Sheet22

Cell Formulas
Range	Formula
B3	=SUMPRODUCT((EDATE($B$1,-$B$2))*B6:P6*(B5:P5<=$B$1))

 

[krodriguez]

Thanks, but not sure what your cells are referencing to. can you add an image/sheet.

 

[krodriguez]

Thank you, DRSteele!!! this works perfectly and it's dynamic

 

[DRSteele]

So am I! Ha.

I'm glad it worked.