software

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/2006Kenyon Clinic07398Chest-- PA amp; Lateral12 PM50
1/3/2006Kenyon Clinic07398Chest-- PA amp; Lateral1 PM75
1/3/2006Kenyon Clinic07398Chest-- PA amp; Lateral5 PM123
1/4/2006Kenyon Clinic07225Shoulder 2vw7 AM89
1/4/2006Kenyon Clinic07405Knee 2vw AP/Lat1 PM56

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)amp;quot;:quot;amp;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)amp;quot;:quot;amp;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 onRegards,

Peo Sjoblomquot;Vickiquot; wrote:

gt; How can I create a formula in a worksheet and/or Pivot Table where I can
gt; figure out an average based on how many certain weekdays fall within that
gt; month. For instance here is a sample:
gt; 1/3/2006Kenyon Clinic07398Chest-- PA amp; Lateral12 PM50
gt; 1/3/2006Kenyon Clinic07398Chest-- PA amp; Lateral1 PM75
gt; 1/3/2006Kenyon Clinic07398Chest-- PA amp; Lateral5 PM123
gt; 1/4/2006Kenyon Clinic07225Shoulder 2vw7 AM89
gt; 1/4/2006Kenyon Clinic07405Knee 2vw AP/Lat1 PM56
gt;
gt; I need to summarize these numbers for an entire year . They want to
gt; summarize it by the hour for each day. Then I need to come up with an
gt; average where I need to use the number of each weekday within each month as
gt; the denominator. So, if I had data for three out of five Mondays in a month,
gt; then I need to take my summary and divide that by five to get a true figure.
gt; Right now I am only getting the summary divided by three to get my average
gt; and that is not what they want. Any ideas or tricks up anyone's sleeve? How
gt; can I set up a formula that will figure out how many Sundays, Mondays, etc.
gt; 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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=544467
Or even simpler...

=4 (DAY(A1 35-WEEKDAY(A1-2))gt;7)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=544467