QTD Formula

[woodams]

I am currently using the formula below to sum a group of columns (months) to get a YTD total based on the current month entered in cell $D$3

=SUM(OFFSET(C8,0,0,1,MONTH($D$3)))

This works great !

However - I am having trouble getting a QTD result ...

I am hoping someone has a formula to calculate QTD values based the month entered in cell $D$3 ?

Thank you !!!

 

[NateO]

Hello, welcome to the board.

Assuming inputting the beginning of the quarter and periods not recording results are empty cells:

Whatever.xls
	A	B	C	D	E	F	G	H	I	J	K	L
3			YTD	Oct	$1,100.00
4			QTD	Jul	$480.00
5
6
7	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
8	$20.00	$40.00	$60.00	$80.00	$100.00	$120.00	$140.00	$160.00	$180.00	$200.00	$220.00	$240.00
Sheet4

Bon chance.

 

[woodams]

Thank you very much for the help with this formula -

and such a quick reply ...

However,

I am wondering if anyone has a suggestion on how to do the QTD formula that would work anytime throughout the year based on a chosen month.

Example:
Jan Feb
2 3
QTD Feb: 5
ray:

 

[NateO]

Change the input to a number and either headers or look into the following in your match function:

=TEXT(d4,"mmm")

Edit: Wait what are you saying? How could Feb be 5? There's only 4 quarters in a year? Are you talking calendar quarters? Me confused...

One thing's for certain, avoid Offset().

I think you may want to reconsider your thinking as to how this is done. If you simply want to grab any single Callendar quarter, or a shifted one due to fiscal reporting, use the logic in my previous post. Otherwise I find the logic of your quandary to be opaque... Are you talking about year-over-year comparisons?

 

[Yogi Anand]

Thank you very much for the help with this formula -

and such a quick reply ...

However,

I am wondering if anyone has a suggestion on how to do the QTD formula that would work anytime throughout the year based on a chosen month.

Example:
Jan Feb
2 3
QTD Feb: 5
ray:

Hi woodams:

Can you clarify what you mean by QTD value -- how about using the figures in Nate's post, what do you consider to be the QTD value, say for May.

 

[woodams]

Thank you very much for all the help. Let me try and be more specific with my question. The answer I should get for QTD July Cell E4 is 260. The formula should sum the current quarter to date based on the current month from cell D4. Therefore, the formula must only add up the number of cells in the current QTR in order to get a QTD answer.

I am really stumped - but I am also very grateful for all the help I have already receivied.

Thank you again,

Jim

QTD & YTD Formula.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3			YTD	Oct	1100
4			QTD	Jul	480
5
6
7	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
8	20	40	60	80	100	120	140	160	180	200	220	240
Sheet1

 

[Yogi Anand]

Thank you very much for all the help. Let me try and be more specific with my question. The answer I should get for QTD July Cell E4 is 260. The formula should sum the current quarter to date based on the current month from cell D4. Therefore, the formula must only add up the number of cells in the current QTR in order to get a QTD answer.

I am really stumped - but I am also very grateful for all the help I have already receivied.

Thank you again,

Jim

QTD & YTD Formula.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3			YTD	Oct	1100
4			QTD	Jul	480
5
6
7	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
8	20	40	60	80	100	120	140	160	180	200	220	240
Sheet1

Hi woodams:

May be I am missing something here -- Jul is the beginning of the 3rd Quarter, so shouldn't the QTD vlaue be 140? If this is so then you should find the following odf some interest ...

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
3			YTD	Oct	1,100.00
4			QTD	Jul	140.00
5
6
7	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
8	20	40	60	80	100	120	140	160	180	200	220	240
9
10	20	60	120	200	300	420	560	720	900	1100	1320	1560
Sheet12

The formula in cell E4 is ...

=SUM(A8:INDEX(A8:L8,MATCH(D4,A7:L7,0)))-IF(MATCH(D4,A7:L7,0)<4,0,SUM(A8:INDEX(A8:L8,INT(MATCH(D4,A7:L7,0)/3)*3-(MOD(E12,3)=0)*3)))

I am sure the formula can be simplified -- anyway first please post back and advise what should be the correct value for Jul!

 

[woodams]

Yogi you are correct the correct calculated value for July should be 140, when Aug is input into cell D4 the resulting value should be 300. And another example would be Feb with a resulting QTD value of 60.

Again - I apoligize for any confusion ...

Thank you again,

 

[Yogi Anand]

Hi woodams:

The formulation I have posted does correctly give 300 for Aug, and 60 for Feb as shown in the following illustrations ...

y031128h1.xls
	A	B	C	D	E	F	G	H	I	J	K	L
3			YTD	Oct	1,100.00
4			QTD	Aug	300.00
5
6
7	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
8	20	40	60	80	100	120	140	160	180	200	220	240
9
10	20	60	120	200	300	420	560	720	900	1100	1320	1560
Sheet12

and

y031128h1.xls
	A	B	C	D	E	F	G	H	I	J	K	L
3			YTD	Oct	1,100.00
4			QTD	Feb	60.00
5
6
7	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
8	20	40	60	80	100	120	140	160	180	200	220	240
9
10	20	60	120	200	300	420	560	720	900	1100	1320	1560
Sheet12

I hope this helps.

 

[woodams]

Yogi -

Thank you agian for your help ...

Can you show me what formulas you have in Row 12 - they are not showing up on the HTML post. After I get this portio of the spreadsheet I would try and consolidate the formula into one cell.

Regards,

Jim