How can I create a formula in a worksheet and/or Pivot Table where I can
figure out an average based on how many certain weekdays fall within that
month. For instance here is a sample:
1/3/2006 Kenyon Clinic 07398 Chest-- PA & Lateral 12 PM 50
1/3/2006 Kenyon Clinic 07398 Chest-- PA & Lateral 1 PM 75
1/3/2006 Kenyon Clinic 07398 Chest-- PA & Lateral 5 PM 123
1/4/2006 Kenyon Clinic 07225 Shoulder 2vw 7 AM 89
1/4/2006 Kenyon Clinic 07405 Knee 2vw AP/Lat 1 PM 56

I need to summarize these numbers for an entire year . They want to
summarize it by the hour for each day. Then I need to come up with an
average where I need to use the number of each weekday within each month as
the denominator. So, if I had data for three out of five Mondays in a month,
then I need to take my summary and divide that by five to get a true figure.
Right now I am only getting the summary divided by three to get my average
and that is not what they want. Any ideas or tricks up anyone's sleeve? How
can I set up a formula that will figure out how many Sundays, Mondays, etc.
in each given month and then use that in my formula?

Put the first date of of the month in A1

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR($A$1),MONTH($A$1), 1)&":"&DATE(YEAR($A$1),MONTH($A$1)+1,0))),2)=1))

will give you the number of Mondays in that month

change the =1 to =2 and you'll get Tuesdays

if you want to check Mondays for all months in 2006, put 01/01/06 in A1 and
use

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR($A$1),ROWS($A$1:A1 ),1)&":"&DATE(YEAR($A$1),ROWS($A$1:A1)+1,0))),2)=1 ))


copy down 12 rows and you'll get Jan, Feb, Mar and so on


Regards,

Peo Sjoblom


"Vicki" wrote:

How can I create a formula in a worksheet and/or Pivot Table where I can
figure out an average based on how many certain weekdays fall within that
month. For instance here is a sample:
1/3/2006 Kenyon Clinic 07398 Chest-- PA & Lateral 12 PM 50
1/3/2006 Kenyon Clinic 07398 Chest-- PA & Lateral 1 PM 75
1/3/2006 Kenyon Clinic 07398 Chest-- PA & Lateral 5 PM 123
1/4/2006 Kenyon Clinic 07225 Shoulder 2vw 7 AM 89
1/4/2006 Kenyon Clinic 07405 Knee 2vw AP/Lat 1 PM 56

I need to summarize these numbers for an entire year . They want to
summarize it by the hour for each day. Then I need to come up with an
average where I need to use the number of each weekday within each month as
the denominator. So, if I had data for three out of five Mondays in a month,
then I need to take my summary and divide that by five to get a true figure.
Right now I am only getting the summary divided by three to get my average
and that is not what they want. Any ideas or tricks up anyone's sleeve? How
can I set up a formula that will figure out how many Sundays, Mondays, etc.
in each given month and then use that in my formula?

If you have the 1st of the month in A1 this formula will give the number
of Mondays in that month

=INT((WEEKDAY(A1-2)+31-DAY(A1+31))/7)

replace the 2 with 3 for Tuesday etc.


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=544467

Or even simpler...

=4+(DAY(A1+35-WEEKDAY(A1-2))7)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=544467