Formula to Calculate QTD and YTD

[mackcracknsack]

Hi,

I am looking for a formula to calculate QTD and YTD data.

Brief explanation of my model, it will contain 3 years of actuals (2015-2017) and 1 year of current budget/plan data, all of which are broken out monthly. I will have 4 QTD calcs (actuals & plan) and 4 YTD calcs.
I also have a lead sheet to enter the current month which will determine the calculation for both YTD (sum x amount of periods for March, April, Sept etc) once cell B5 is changed. The month entry on my lead sheet also works out the current Qtr which will calculate QTD for Q1 (Jan-Mar) or 2 months of Qtr 2 (Apr & May)

The year to date calculation will be cumulative and just keep adding up the months of the year
The QTD calc will contain only 1,2 or 3 months data for the current quarter all being determined by the inputted month

[TABLE="width: 1310"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]z[/TD]
[TD][/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mar[/TD]
[TD]Q1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]2017 Plan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD][/TD]
[TD]2017 QTD[/TD]
[TD]2017 YTD[/TD]
[TD]2017 Plan QTD[/TD]
[TD]2017 Plan YTD[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Gross Sales[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Net Sales[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Expenses[/TD]
[TD]-1[/TD]
[TD]-2[/TD]
[TD]-3[/TD]
[TD]-1[/TD]
[TD]-2[/TD]
[TD]-3[/TD]
[TD]-1[/TD]
[TD]-2[/TD]
[TD]-3[/TD]
[TD]-1[/TD]
[TD]-2[/TD]
[TD]-3[/TD]
[TD]-1[/TD]
[TD]-2[/TD]
[TD]-3[/TD]
[TD]-1[/TD]
[TD]-2[/TD]
[TD]-3[/TD]
[TD]-1[/TD]
[TD]-2[/TD]
[TD]-3[/TD]
[TD]-1[/TD]
[TD]-2[/TD]
[TD]-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Thanks for help

 

[jorismoerings]

Hi,

see if this helps:

Excel 2016 (Windows) 64 bit
	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	AD
1		Mar	Q1
2			2017												2017 Plan
3			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	2017 QTD	2017 YTD	2017 Plan QTD	2017 Plan YTD
4		Gross Sales	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	6	6	6	6
5		Net Sales	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	6	6	6	6
6		Expenses	-1	-2	-3	-1	-2	-3	-1	-2	-3	-1	-2	-3	-1	-2	-3	-1	-2	-3	-1	-2	-3	-1	-2	-3	-6	-6	-6	-6
Sheet1

Cell Formulas
Range	Formula
AA4	=SUM(OFFSET($C4,0,((ROUNDUP(MATCH($B$1,C$3:N$3,0)/3,0)-1)*3),,MATCH($B$1,C$3:N$3,0)-((ROUNDUP(MATCH($B$1,C$3:N$3,0)/3,0)-1)*3)))
AB4	=SUM(OFFSET($C4,0,0,1,MATCH($B$1,$C$3:$N$3,0)))
AC4	=SUM(OFFSET($O4,0,((ROUNDUP(MATCH($B$1,O$3:Z$3,0)/3,0)-1)*3),,MATCH($B$1,O$3:$Z$3,0)-((ROUNDUP(MATCH($B$1,O$3:Z$3,0)/3,0)-1)*3)))
AD4	=SUM(OFFSET($O4,0,0,1,MATCH($B$1,$C$3:$N$3,0)))

 

[RicardoCubed]

I was surfing through posts and saw this.
Wanted to thank you as I had a situation where this approach was very insightful and worked for me.
Appreciate your time!

 

[mackcracknsack]

Thanks a million,
This formula works like a charm!!!!

 

[mackcracknsack]

Hey,

I am trying to do a similar calculation in my table but for Q1, Q2 , Q3 & Q4.
The way my formulas work in my tables, some data has already flowed into May but I do not want to factor this into my Qtr2 calc as it should only contain 1 month of data i.e April.

The calculation will all be based from the helper cell in B1 with current month selected.
Hopefully the formulas can be set up for Qtr3 and Qtr4 and they will be populated once we hit those months.