Prev: If Statement with 9 sequences
Next: convert to minutes
From: DOUG on 7 Apr 2010 11:19
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
From: Jim Thomlinson on 7 Apr 2010 11:34
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
From: JLatham on 7 Apr 2010 11:53
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
From: Dave Peterson on 7 Apr 2010 11:57
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/assistance/2002/articles/xlconPT101.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
From: Herbert Seidenberg on 7 Apr 2010 14:25
Excel 2007 PivotTable
Sum 1st weekday(s) of month(s), year(s).
With multiple sheets.
With macro.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_07_10.xlsm
Pdf preview:
http://www.mediafire.com/file/fml2teodznn/04_07_10.pdf
 | 
Pages: 1
Prev: If Statement with 9 sequences
Next: convert to minutes