Adding a column to calculate an average in a pivot table

[petehz]

Hi All,

I've created a pivot table of the first 2 columns in the table below and I'd like to add another column to calculate the average spans for each function based on the individual manager's spans witthin that fucntion. I know I can do this manually but is there a quick way to do it within the pivot table itself? Thanks

 

[Dave Patton]

with Excel 365, you can try Groupby.
You can also review Pivotby

Consider the following

T202503a.xlsm
	A	B	C	D	E	F	G
1	Business Group	Business	Reports
2	Admin	Admin Manager A	a-1	Business Group	Business	Reports	Average
3	Admin	Admin Manager A	a-2	Admin	Admin Manager B	7
4	Admin	Admin Manager A	a-3	Admin	Admin Manager A	3
5	Admin	Admin Manager B	b-1	Admin		10	5
6	Admin	Admin Manager B	b-2	IT	IT Manager C	6
7	Admin	Admin Manager B	b-3	IT	IT Manager D	8
8	Admin	Admin Manager B	b-4	IT	IT Manager E	3
9	Admin	Admin Manager B	b-5	IT		17	5.7
10	Admin	Admin Manager B	b-6	Grand Total		27
11	Admin	Admin Manager B	b-7
12	IT	IT Manager C	c-1
13	IT	IT Manager C	c-2
14	IT	IT Manager C	c-3
15	IT	IT Manager C	c-4
16	IT	IT Manager C	c-5
17	IT	IT Manager C	c-6
18	IT	IT Manager D	d-1
19	IT	IT Manager D	d-2
20	IT	IT Manager D	d-3
21	IT	IT Manager D	d-4
22	IT	IT Manager D	d-5
23	IT	IT Manager D	d-6
24	IT	IT Manager D	d-7
25	IT	IT Manager D	d-8
26	IT	IT Manager E	e-1
27	IT	IT Manager E	e-2
28	IT	IT Manager E	e-3
29
2a

Cell Formulas
Range	Formula
D2:F10	D2	=GROUPBY(A1:B28,C1:C28,COUNTA,3,2)
G5	G5	=F5/COUNTA(E3:E4)
G9	G9	=F9/COUNTA(E7:E9)
Dynamic array formulas.

 

[petehz]

with Excel 365, you can try Groupby.
You can also review Pivotby

Consider the following

T202503a.xlsm
	A	B	C	D	E	F	G
1	Business Group	Business	Reports
2	Admin	Admin Manager A	a-1	Business Group	Business	Reports	Average
3	Admin	Admin Manager A	a-2	Admin	Admin Manager B	7
4	Admin	Admin Manager A	a-3	Admin	Admin Manager A	3
5	Admin	Admin Manager B	b-1	Admin		10	5
6	Admin	Admin Manager B	b-2	IT	IT Manager C	6
7	Admin	Admin Manager B	b-3	IT	IT Manager D	8
8	Admin	Admin Manager B	b-4	IT	IT Manager E	3
9	Admin	Admin Manager B	b-5	IT		17	5.7
10	Admin	Admin Manager B	b-6	Grand Total		27
11	Admin	Admin Manager B	b-7
12	IT	IT Manager C	c-1
13	IT	IT Manager C	c-2
14	IT	IT Manager C	c-3
15	IT	IT Manager C	c-4
16	IT	IT Manager C	c-5
17	IT	IT Manager C	c-6
18	IT	IT Manager D	d-1
19	IT	IT Manager D	d-2
20	IT	IT Manager D	d-3
21	IT	IT Manager D	d-4
22	IT	IT Manager D	d-5
23	IT	IT Manager D	d-6
24	IT	IT Manager D	d-7
25	IT	IT Manager D	d-8
26	IT	IT Manager E	e-1
27	IT	IT Manager E	e-2
28	IT	IT Manager E	e-3
29
2a

Cell Formulas
Range	Formula
D2:F10	D2	=GROUPBY(A1:B28,C1:C28,COUNTA,3,2)
G5	G5	=F5/COUNTA(E3:E4)
G9	G9	=F9/COUNTA(E7:E9)
Dynamic array formulas.

Thanks Dave, much appreciated