In a pivot table, can I adjust the logic for calculating subtotal averages?

[jjjason10]

Good Afternoon All,

I've spent the last few hours searching for this issue with no luck, so I apologize if this has been posted already.

If I add a subtotal to a pivot table and make the subtotal calculate as "Average", I'm wondering if there is a way to adjust how excel calculates the average. Below is a snippet of sample data, similar to my pivot table. I have a few sales teams where I show each individuals total, then there is a roll up into the team total. I also have it set up so that I can see the team average. However my data set feeding the table has a line of data for each day of the sales cycle. As a reference, TEAM A has 383 lines of data in the date set. It appears that when I add in the subtotal Average, excel is calculating the average based on the number of lines of data in the data set and not the number of employees on the team. The numbers in Red font to the right of the table are my expected resultes (Taking the sum divided by 6). Is there anyway I can acheive this with in the table, and not use formulas in external columns.

Book1
	A	B	C	D	E	F	G
3	Row Labels	Sum of Leads	Sum of Contacted	Sum of Sale
4	North	1062.00	678.00	265.00
5	TEAM A
6	Employee 1	77.00	58.00	23.00
7	Employee 2	96.00	90.00	35.00
8	Employee 3	83.00	73.00	21.00
9	Employee 4	124.00	73.00	39.00
10	Employee 5	96.00	81.00	21.00
11	Employee 6	81.00	51.00	18.00
12	TEAM A Sum	557.00	426.00	157.00
13	TEAM A Average	1.45	1.11	0.41	93	71	26
14	TEAM B
15	Employee 1	175.00	79.00	38.00
16	Employee 2	93.00	58.00	32.00
17	Employee 3	91.00	48.00	18.00
18	Employee 4	87.00	49.00	19.00
19	Employee 5	59.00	17.00	1.00
20	Employee 6	0.00	1.00	0.00
21	TEAM B Sum	505.00	252.00	108.00
22	TEAM B Average	1.84	0.92	0.39	84	42	18
23	Grand Total	1062.00	678.00	265.00
Sheet3

Cell Formulas
Range	Formula
E13	=B12/6
E22	=B21/6
F13	=C12/6
F22	=C21/6
G13	=D12/6
G22	=D21/6

 

[oldbrewer]

in my post 9 reps did change teams, and the approach is dynamic in that as you add new rows of data the summary tables automatically update.....