Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Patient Demand on X Day of the Month
I need to summarize and compare patient demand for appointments going back
over three years. I can pull the data and display the days of the week. In Ms Excel, is there a way to line up the same days of each month - but not the dates? For instance, I need to line up and average all of the first Mondays in May, all of the first Tuesdays in May and so on. DOUG in Wichita |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Patient Demand on X Day of the Month
You are going to run into problems with that. This year April has 5 Fridays.
Last year it only had 4 Fridays. How would you like to deal with that type of situation? You may be better off matching 52 weeks this year over a comperable 52 weeks last year. At that point things get a lot easier to work with. -- HTH... Jim Thomlinson "DOUG" wrote: I need to summarize and compare patient demand for appointments going back over three years. I can pull the data and display the days of the week. In Ms Excel, is there a way to line up the same days of each month - but not the dates? For instance, I need to line up and average all of the first Mondays in May, all of the first Tuesdays in May and so on. DOUG in Wichita |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Patient Demand on X Day of the Month
And, if you didn't already mean to do it this way, he could split it out like:
2010 2009 1st WK Mon Tue Wed Thur Fri 2nd WK the first and last weeks of the year(s) are a little troublesome, since 2009 started on a Thursday and 2010 started on a Friday (and 2009 also ended on Thursday, while 2010 will end on a Friday), but a 'common sense' examination of the data would take that into consideration. "Jim Thomlinson" wrote: You are going to run into problems with that. This year April has 5 Fridays. Last year it only had 4 Fridays. How would you like to deal with that type of situation? You may be better off matching 52 weeks this year over a comperable 52 weeks last year. At that point things get a lot easier to work with. -- HTH... Jim Thomlinson "DOUG" wrote: I need to summarize and compare patient demand for appointments going back over three years. I can pull the data and display the days of the week. In Ms Excel, is there a way to line up the same days of each month - but not the dates? For instance, I need to line up and average all of the first Mondays in May, all of the first Tuesdays in May and so on. DOUG in Wichita |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Patient Demand on X Day of the Month
I'm assuming that you have a date field on each row -- and that date field
contains a real date. If yes, then I'd add a couple of helper columns so that I could use them to drill down to what I need. I'd insert 3 new columns A:C. This will shift all the data over 3 columns. Add headers to A1:C1: A1: Day.# B1: Year C1: Month Then with the date in D1, put these 3 formulas in A2:C2: A2: =TEXT(D2,"ddd.")&INT((DAY(D2)-1)/7)+1 B2: =Year(d2) C2: =Month(d2) You may never need the year and month, but some day, you may be happy it's there. Then drag these 3 formulas down as far as you need. Now you can build a pivottable based on your data and these 3 helper columns. Select your range (A1:e999 or whatever you're using. I had qty in column E.) Data|pivottable (in xl2003 menus) Follow the wizard until you get to the step with the Layout button on it. Click that Layout button Drag the Year header to the page field drag the month header to the page field (under the year) Drag the Day.# header to the row field Drag the Qty field (or whatever your header is) to the data field. doubleclick on that "button" in the data field and change it to Average. And finish up the wizard. Now you can enjoy the benefits of the pivottable. You can use the page fields to show just certain years or certain months (or certain years and months) and see what changes. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx DOUG wrote: I need to summarize and compare patient demand for appointments going back over three years. I can pull the data and display the days of the week. In Ms Excel, is there a way to line up the same days of each month - but not the dates? For instance, I need to line up and average all of the first Mondays in May, all of the first Tuesdays in May and so on. DOUG in Wichita -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Patient Demand on X Day of the Month
Excel 2007 PivotTable
Sum 1st weekday(s) of month(s), year(s). With multiple sheets. With macro. http://c0718892.cdn.cloudfiles.racks.../04_07_10.xlsm Pdf preview: http://www.mediafire.com/file/fml2teodznn/04_07_10.pdf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting patient check in | Excel Worksheet Functions | |||
Current Patient Count | Excel Worksheet Functions | |||
Supply and Demand Charting | Charts and Charting in Excel | |||
demand planning templates | Excel Worksheet Functions | |||
I need to exclude duplicate patient names for dates of service is. | Excel Discussion (Misc queries) |