Average of monthly count

Emmy

New member
Joined
Jun 27, 2014
Messages
6
Reaction score
0
Points
0
Hi,

I'm trying to recreate a table using a pivot table but am struggling with one final element. The normal table shows the number of reports written by analyst per month for each year, with a total and average for the year.
2012
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Average
Analyst1 0 0 7 3 3 3 4 4 4 6 5 0 39 4
Analyst2 13 21 32 17 19 14 21 19 22 33 23 0 234 20
Analyst3 9 18 21 12 15 12 16 17 18 23 22 0 183 15
Analyst4 6 12 16 8 13 8 12 13 14 19 13 0 134 11
Total 28 51 76 40 50 37 53 53 58 81 63 0 590 12

I have recreated this whole table successfully apart from the last column - the average for the year. In the normal table it is an average formula of the Jan-Dec values but I can't work out how to do this in the pivot table - any pointers?

Thanks,

Emmy
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
The easiest way is to add a helper column to the table to get the average: e.g =AVERAGE(B3:M3) copied down.
 

Emmy

New member
Joined
Jun 27, 2014
Messages
6
Reaction score
0
Points
0
Hi,
Thanks for looking at this.
I'm not sure if I've misunderstood you but I'm looking to get rid of the normal table altogether so that I have the underlying data and the pivot table only.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Isn't the "normal table" and the "underlying data" the same thing?

I was suggesting you add the formula to the right side of the pivot table and reference the pivot table rows.
 

Emmy

New member
Joined
Jun 27, 2014
Messages
6
Reaction score
0
Points
0
No, the underlying data and the normal table aren't the same - the underlying data is stored in this format:

Date Company Writer
03/01/2012 Company 1 Analyst3
05/01/2012 Company 2 Analyst3
17/01/2012 Company 3 Analyst2
19/01/2012 Company 4 Analyst2
20/01/2012 Company 5 Analyst3
23/01/2012 Company 6 Analyst2
23/01/2012 Company 7 Analyst3
23/01/2012 Company 8 Analyst4
23/01/2012 Company 9 Analyst2
23/01/2012 Company 10 Analyst2
25/01/2012 Company 11 Analyst2
25/01/2012 Company 12 Analyst2
25/01/2012 Company 13 Analyst4
25/01/2012 Company 14 Analyst4
26/01/2012 Company 15 Analyst4
26/01/2012 Company 16 Analyst3
26/01/2012 Company 17 Analyst4
27/01/2012 Company 18 Analyst3
27/01/2012 Company 19 Analyst3
27/01/2012 Company 20 Analyst2

The normal table was using countif formulas to get the number of reports by analyst for each month.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Ok, so you are creating the PT from the Underlying table. The Average() function is in the PT.
 

Emmy

New member
Joined
Jun 27, 2014
Messages
6
Reaction score
0
Points
0
So how would I get the average per month for each year?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Post a sample workbook with fake data, and show a sample of expected results.
 

Emmy

New member
Joined
Jun 27, 2014
Messages
6
Reaction score
0
Points
0
I can't seem to upload the file (even though it's only 53KB). I've created a pivot table which exactly mirrors my table but I can't get the average column to work as I can only average the underlying data and not the average of the number of reports per month which are in the pivot table.
 

Emmy

New member
Joined
Jun 27, 2014
Messages
6
Reaction score
0
Points
0
Any ideas anyone? My pivot tables show the count of reports per month for each year - is there anyway I can get the average of these in a subtotal column for each year?
 
Top