Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to see trends year to year in the number of meals served at our diner in order to predict staffing and ordering.

The best arrangement would be to compare a specific day on either side of a holiday. For example, how did we do on the Monday after thanksgiving in all past years.

I realize the holidays shift so it might be hard to set up, so I could also just compare the nth (name)day of the month. For instance, how did we do on the second Saturday of February in all previous years.

How can I do this?

share|improve this question
2  
There's not enough information to answer the question. You haven't specified the layout of your excel spreadsheet or anything. –  Simkill Jan 28 '13 at 16:14
    
I agree that you didn't give much to go on, but I answered anyways, because it's a fun question. –  Doug Glancy Jan 29 '13 at 5:06
add comment

1 Answer

I imagine I'd use a pivot table. So, assuming my data was in a table that looks like this...

enter image description here

... here's how I'd set it up:

Column A: contains your dates

Column B: formula to extract the year - =YEAR([@Date])

Column C: formula to extract the month number: =MONTH([@Date])

Column D: formula to show the month name: =TEXT([@Date],"MMM")

Column E: formula to extract the day number: =WEEKDAY([@Date])

Column F: formula to show the day name: =TEXT([@Date],"DDDD")

Column G: formula to calculate the occcurrence of the week in that month and year:

=COUNTIFS(B$1:B2,YEAR([@Date]),C$1:C2,MONTH([@Date]),E$1:E2,WEEKDAY([@Date]))

Column H: contains your sales

Then make a pivot table from this table, and arrange it like this:

enter image description here

The result will look like this:

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.