year to date comparison

[AverageJoe2015]

Hi Everyone,

I will try to make this question as simple as possible. I'm currently working on a spreadsheet that I would like to compare year to date information against last year. The data is done on a quarterly basis meaning Jan:Mar = Q1, Apr:Jun = Q2, Jul:Sep = Q3 and Oct:Dec = Q4. I have placed a formula that if I open the spreadsheet today it will automatically recognize it as of today's date and will make another formula as of last month's month end date (we work on a month lag). My problem is if I'm working on the spreadsheet in August, I would like it to automatically recognize we are working on YTD on Q3 and take last year's Sum of quarter's (Q1:Q3) to compare YTD information. I have placed corresponding year to date numbers in a section that will not be visible to the reader. I would like to have the specific cell automatically calculate the value without updating it every single month. Please see attached spreadsheet to get an understanding of what I am trying to accomplish. If anyone has any ideas it will be greatly appreciated.

Many thanks!

[Glenn Kennedy]

I'd be tempted to take a different approach, like this, and (in the hidden rows) use a series of nested IFs to get a simpler overall structure.

[ChemistB]

What do you do when Q3 comes along, insert cells between J5 and K10?

[bebo021999]

I suppose not to use extra YTD calculation from row 13 to 20
I insert 2 more columns for Q3 and Q4 of 2015, then in N7:

Option 1: Using B1 cell:

Please Login or Register  to view this content.

Option 2: Use today's date (donot use B1 cell):

Please Login or Register  to view this content.

[AverageJoe2015]

Glenn,

This is perfect! Thank you so much!

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.