Excel Pivot with Averages

A sample workbook will go a long way to solving the issue.

However, based on the description, I assume you have a helper column that has a formula like =Text([@[Date]],"ddd"). And what you are trying to do is use a count of the day of the week to compute the averages. There is a quirk in pivot tables that reports count as a number but you can't use it in calculations. The solution may be another helper column with the formula =1. Then use this value as a sum by day of the week.

Use this column instead of count of day of the week. If you are still having a problem, post your data. I am guessing in the dark.

Please utilize the information in the "HOW TO ATTACH YOUR SAMPLE WORKBOOK" banner at the top of the page to post the .xlsx file from which the screen shots in the .docx file were taken.
Let us know if you have any questions.

Perhaps the Excel table and pivot table modeled on Sheet1 will provide the information needed:
1. Extract the Date, Day and Month columns from the table on the Admissions Long Sheet using Power Query:

Please Login or Register  to view this content.

2. Add column D, Count of Appointments, which is populated using: =IF(COUNTIFS(INDEX([Date],1):[@Date],[@Date])=1,COUNTIFS([Date],[@Date]),"")
3. Add column E, Count of Weekday, which is populated using: =IF(COUNTIFS(INDEX([Date],1):[@Date],[@Date])=1,1,"")
4. Produce the pivot table with Day of Week in the Rows area and the two added columns in the Values area using the Month column in the Filters area
5. Add a Calculated Field: ='Count of Appointments'/'Count of Weekday'
Let us know if you have any questions.

Here is my shot at it. I have two "helper columns"

One of them is called Unique DOW =1/COUNTIF([Date],[@Date]) - this spreads out the distribution for a date so that when all records with this date are summed they equal 1. In other words when you add up all the Mondays, each Monday in the month contributes 1 to the count.

The other is One =1

The average is One / Unique DOW (total appointments / number of that day of the week)

We need two numbers: the total number of records for dates associated with Mondays and the number of Mondays in the month.

Gathering the total number of records associated with the Mondays in a month is straightforward.

We need trick Excel into getting the number of Mondays in the month.

For example: suppose there are four Mondays in a month and the first one has one record and the second two records and the third three records and the fourth four records.

Unique DOW =1/COUNTIF([Date],[@Date])

For the 1st Monday this is 1/1 = 1 and there is only one record with the date so when the figure is summed up it equals 1.

For the 2nd Monday this is 1/2 = 0.5 and there are two records with this date so when the figure is summed up it equals 1

For the 3rd Monday this is 1/3 = 0.333 and there are three records with this date so when the figure is summed up it equals 1

For the 4th Monday this is 1/4 = 0.25 and there are 4 records with this date so when the figure is summed up it equals 1

The sum of all these figures is 4 which is the number of Mondays in the month.

A calculated field yields the average.