How to calculate MTD and YTD Variance

[jkang22]

I have a set of Months Actual and Months Budget. I'd like to calculate the MTD and YTD variance in a specific cell. For the MTD variance, i'd like for it to calculate the month in which we are presently in.

ive copied the spreadsheet below... PLEASE HELP!

8/1/2012 FY13 Budget FY 13 Actual JUL AUG SEP OCT NOV DEC JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC JAN FEB MAR APR MAY JUN MTD Variance YTD Variance 50 50 150 100 200 50 50 50 75 85 60 90 40 60 <colgroup><col><col span="25"><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[ChrisM]

Try:

=SUM(OFFSET(G7,,,,H3))

Where G7 is the first month value you are trying to measure, the cell with 50 in August for example, and where H3 is an integer showing which month of the year you are in. So in your budget example if you set H3 = 4, this would give you the YTD budget of Aug-thru-Nov.

If you don't want to manually type in the number of the current month, you could use =Month(Now) to return the number 1 thru 12 in cell H3. But that would only work if your year starts with January. You seem to start with Jul so you'd have to modify that a bit.

 

[texasalynn]

try this

Excel 2003
	AA	AB	AC
4	MTD Variance		YTD Variance
5
6	10		0
Sheet4

Cell Formulas
Range	Formula
AA6	=SUMPRODUCT(--($O$4:$Y$4=TEXT($A$1,"mmm")),($O$6:$Y$6))-SUMPRODUCT(--($B$4:$L$4=TEXT($A$1,"mmm")),($B$6:$L$6))
AC6	=SUM(INDIRECT("N6"&":"&CHAR(77+MATCH(TEXT(A1,"mmm"),N4:Y4,0))&"6"))-SUM(INDIRECT("A6"&":"&CHAR(64+MATCH(TEXT(A1,"mmm"),A4:L4,0))&"6"))

 

[jkang22]

Texasalynn, the YTD does not work. It is spitting out -550, but it should be 0 since ytd as of now (august) actuals v. budget variance is 0.

Also, is this formula factoring in today's date (AA1)? I would like for it to factor in today's date to know what specific month to calculate for MTD and also to know what months to sum for YTD. So for example, as of present, i would like for it to calculate YTD for months july & aug as it is 8/1 today.

Thanks for all your help i am really clueless

try this

Excel 2003

	AA	AB	AC
4	MTD Variance		YTD Variance
5
6	10		0

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula AA6 =SUMPRODUCT(--($O$4:$Y$4=TEXT($A$1,"mmm")),($O$6:$Y$6))-SUMPRODUCT(--($B$4:$L$4=TEXT($A$1,"mmm")),($B$6:$L$6)) AC6 =SUM(INDIRECT("N6"&":"&CHAR(77+MATCH(TEXT(A1,"mmm"),N4:Y4,0))&"6"))-SUM(INDIRECT("A6"&":"&CHAR(64+MATCH(TEXT(A1,"mmm"),A4:L4,0))&"6")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[texasalynn]

That's weird because my test shows 0 for ytd. Here is the complete layout. I have today's date in cell A1 and is used in the formula to check for the current month
Excel 2003

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	8/2/2012
2
3	FY13 Budget													FY 13 Actual
4	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR	MAY	JUN		JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR	MAY	JUN		MTD Variance		YTD Variance
5
6	50	50	150	100	200	50	50	50	75	85	60	90		40	60												10

<TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula A1 =TODAY() AA6 =SUMPRODUCT(--($O$4:$Y$4=TEXT($A$1,"mmm")),($O$6:$Y$6))-SUMPRODUCT(--($B$4:$L$4=TEXT($A$1,"mmm")),($B$6:$L$6)) AC6 =SUM(INDIRECT("N6"&":"&CHAR(77+MATCH(TEXT(A1,"mmm"),N4:Y4,0))&"6"))-SUM(INDIRECT("A6"&":"&CHAR(64+MATCH(TEXT(A1,"mmm"),A4:L4,0))&"6")) <TBODY> </TBODY>

<TBODY>
</TBODY>

 

[jkang22]

so i forgot to mention that my "A1" actually starts at "T3".. there is some info in the spreadsheet i could not include due to confidentiality for my client. I think the problem is because CHAR cannot be used for columns AA and beyond.

So, i used this formula: =SUM(INDIRECT("AG7"&":"&SUBSTITUTE((SUBSTITUTE(ADDRESS(6,34,4),"AH6","32")+MATCH(TEXT($AT$4,"mmm"),$AG$6:$AR$6,0)),"34","AH")&"7"))-SUM(INDIRECT("T7"&":"&CHAR(83+MATCH(TEXT($AT$4,"mmm"),$T$6:$AE$6,0))&"7"))

However when i drag it down, it wont work because its cell specific for the 7th row, meaning for each row i have to change it from "7" to 8,9,10 and so on..

Would you know an easier/shorter formula? or one where i would not need to change for each row? (i have 120 rows so this will get a bit annoying)

 

[texasalynn]

ahh I see the flaw. The other for having "AA" I will have to consider. If you could post a sample of your "scrubbed" data (make it generic not customer related). If you can not use one of the HTML downloads, you can put borders around all of your excel cells and copy paste it into your posts.

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	8/2/2012
2
3	FY13 Budget													FY 13 Actual
4	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR	MAY	JUN		JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR	MAY	JUN		MTD Variance		YTD Variance
5
6	50	50	150	100	200	50	50	50	75	85	60	90		40	60												10		0
7	50	25	150	100	200	50	50	50	75	85	60	90		55	22												10		2
8	45	35	150	100	200	50	50	50	75	85	60	90		47	25												10		-8
9	15	75	150	100	200	50	50	50	75	85	60	90		18	75												10		3
10																											10		0
Invoice Cover Totals

Cell Formulas
Range	Formula
A1	=TODAY()
AA6	=SUMPRODUCT(--($N$4:$Y$4=TEXT($A$1,"mmm")),($N$6:$Y$6))-SUMPRODUCT(--($A$4:$L$4=TEXT($A$1,"mmm")),($A$6:$L$6))
AC6	=SUM(INDIRECT("N"&ROW(A6)&":"&CHAR(77+MATCH(TEXT($A$1,"mmm"),$N$4:$Y$4,0))&ROW(A6)))-SUM(INDIRECT("A"&ROW(A6)&":"&CHAR(64+MATCH(TEXT($A$1,"mmm"),$A$4:$L$4,0))&ROW(A6)))

 

[jkang22]

																																13
																														MONTH
CARRY OVER & NEW CAPITAL PROJECTS OR MULTIPLE YEAR PROJECTS																												AUG
														FY13 PLANNED Spend												FY 13 ACTUAL Spend
Capital Type	PPSO #	Project	Project Manager	Portfolio Manager	Contact Person	GIS Process Lead	Primary Contact	Sent Date	Revised FY13 Capital	Revised FY13 Expense		Notes		JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR	MAY	JUN		JUL	AUG		MTD Variance		YTD Variance	%
									$8,000	$4,000				100	500	600	4000	500			600	500	500	500	200		200	300		$ (200.00)		$ (100.00)
									$500,000	$0																				$ -
									$3,000	$0																				$ -
									$4,400	$0																				$ -
									$85,000	$15,000																				$ -
									$30,000	$5,000																				$ -
									$30,000	$5,000																				$ -
									$55,000	$5,000																				$ -

<colgroup><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col span="12"><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[jkang22]

I deleted all the peripheral information.. i think you can just copy paste the entire thing onto a blank spreadsheet.. hope this works.. thank you so much life saver!

 

[texasalynn]

see if this works for the ytd variance

Excel 2003
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
14	FY13 PLANNED Spend													FY 13 ACTUAL Spend
15	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR	MAY	JUN		JUL	AUG		MTD Variance	YTD Variance	%
16	100	500	600	4000	500			600	500	500	500	200		200	300		($200.00)	($100.00)
17																	$ -
18																	$ -
19																	$ -
20																	$ -
21																	$ -
22																	$ -
23
Invoice Cover Totals

Cell Formulas
Range	Formula
AF16	=-SUM(INDIRECT("N"&ROW(H16)&":"&ADDRESS(16,COLUMN(N1)+MATCH(TEXT($A$1,"mmm"),$A$4:$L$4,0))&ROW(H16)))+SUM(INDIRECT("AB"&ROW(H16)&":"&ADDRESS(16,COLUMN(AA1)+MATCH(TEXT($A$1,"mmm"),$A$4:$L$4,0))))