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

ibddude

New Member
Joined
Aug 20, 2011
Messages
17
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top