I have been looking around and trying to find a solution to this for a couple days and found some things that I am sure will be in the answer but I just am having trouble wrapping my head around how to tie it all together. Sorry if this is a bit long, but I am just trying to get it all across in one fell swoop. Apologies for the formatting from Excel to here.
Here is what I have and am trying to do:
I entered the date and day in this format in multiple sheets in workbook 1 (named 0101, 0102, 0103) from the start of the year to last night (B2:C4):
Date: January 01, 2013
Day: Tuesday
I then entered all the data needed below that (B5:E26):
Time Transactions Total Sales Average Sales
12:00 39 $352.97 $9.05
13:00 53 $416.98 $7.87
14:00 44 $339.42 $7.71
15:00 43 $304.96 $7.09
etc.....
In workbook 2, I have sheets named for each day of the week. I would like to take the information from the previous three weeks for each corresponding day from workbook 1 and put the averaged data into another table with the data all formatted the same in workbook 2. This way, each week when I go into the aggregate file (workbook 2), I can look at the chart I'll create and see the average of the past three weeks transactions, total sales, and average sales for each hour of the business day and be able to staff accordingly (assuming the trend continues).
Thanks in advance for even reading that.
**edit**
I am somewhat fluent in excel and can do a good deal of things, just have gotten myself wanting to do something that is way over my head in this case! Let me know if you would like any files uploaded to tinker with.
Bookmarks