Finding the Monthly Average of a daily "Count" in a Pivot Table

[ibddude]

Hello;

This is a new one for me and I've searched a few hours for a solution and either my question isn't quite worded right, it's not be done (can't believe that), Or I'm having Excel "block" because its so straight forward ...I'm at a loss and could use some help/guidance.

I have the following pivot table (only showing relevant columns)

Row Labels Count of User Name
Jun 101
3-Jun 6
5-Jun 4
6-Jun 10
7-Jun 6
10-Jun 7
12-Jun 4
13-Jun 6
14-Jun 2
.
.
.
28-Jun 4

What I want to calculate and show is the monthly average of this count. If I drag my cursor down the column, I get an average of 5 for the month but I want to be able to show the average on the table so it recalculates when I change the filters automatically. I've tried using "Measures" an may have gotten close but no cigar!

Any help would be greatly appreciated!

Thanks, Mike

 

[MPaul100]

Group the dates column as Months and [FONT=&quot]then in the pivot table, right click the [/FONT]Sum of Amount[FONT=&quot] > [/FONT]Summarize Values By[FONT=&quot] > [/FONT]Average[FONT=&quot]. Now you will get all averages of each month in the Pivot Table. Otherwise, probably, you will have to use a formula to calculate the averages outside the pivot table like:
[/FONT]=AVERAGEIFS(B:B,A:A,">="&F1,A:A,"<="&EOMONTH(F1)) if in F1:F12 you have the months(Jan to Dec as actual dates)

 

[ibddude]

Thanks MPaul100

Thanks for your feedback. What I've discovered is that it appears you can't apply the "Summarize Values as"--> Average works on a pivot table column that is set to count (as opposed to sum). When you do you get a column of #DIV/0!. In my case what I'm trying to do is show the number of records per day and then calculate the average for the month. If I right click and drag down the column say for the week of June, I get the average in the lower banner in the Excel window but I can't find a way to display it in the pivot table. I wouldn't care if each row showed 5.05 for the month of June and then the monthly number was 5.05 but there just doesn't seem to be a way to calculate the average on a "count" that I can find.

Thanks
Mike