Summary of calcs for totals in pivot table

[ammdumas]

Howdy,

Pivot Tables are a bit of a hole in my excel expertise. I know how to use them, but not very well.

The sample sheet below shows a summary row of calculations based on counts of TRUEs and FALSEs in the data. It actually counts 1s and 0 in columns J through M because I do some error catching as well. The Percentage Complete is based on those counts AND the weighted numbers shown above each summary header.

Works great, except now the boss want to see those summary calculations separated by Area (column B). My first thought is pivot tables! Question is how do I get those summary calcs into a pivot table for each area? I saw a post about inserting calculated fields into a pivot table (Formulas...Calculated Field) but I don't seem to have that function available to me (I run Excel 2002).

Any thoughts?

ModelTrack.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
5	Equipment
6	As of Date:	08/02/2007
7				%	50	60	95	100
8	Type	Percentage Complete	Totals	Day	Preliminary	Vendor Preliminary	Verified	Final	Errors
9	EQUI	61.82	198	8	110	51	4	33	0
10										0	0	0	0
11	Tag	Area	Class	Type	Preliminary	Vendor Preliminary	Verified	Final	Errors
12	/26-AR-001	26	MECH	AR	TRUE	TRUE	FALSE	FALSE		0	1	0	0
13	/26-BH-903	26	MECH	BH	TRUE	TRUE	FALSE	FALSE		0	1	0	0
14	/26-CH-001	26	PLTW	CH	TRUE	TRUE	FALSE	FALSE		0	1	0	0
15	/26-CR-001	26	MECH	CR	TRUE	TRUE	FALSE	FALSE		0	1	0	0
16	/26-CV-001	26	MECH	CV	TRUE	TRUE	FALSE	FALSE		0	1	0	0
17	/26-EF-904	26	MECH	EF	TRUE	TRUE	FALSE	FALSE		0	1	0	0
18	/26-FE-001	26	MECH	FE	TRUE	TRUE	FALSE	FALSE		0	1	0	0
Equipment

 

[ammdumas]

Ok. Figured out part of the problem. The created pivot table below shows exactly what I wanted to create (The Grand total at the bottom confirms the numbers are correct) EXCEPT for the Percentage Complete.

If you look at the original post, the Grand Total number should be 61.82. I think this has to do with the fact that the formula I created is SUM OF Percentage complete, not Percentage Complete. That formula needs to be calcuated on the colculate Counts and Sums in the pivot table, not the source data.

How would I do that, short of doing the formula outside of the pivot table?

ModelTrack summary by area.xls
	A	B	C	D	E	F	G
3		Data
4	Area	Count of Tag	Sum of P	Sum of VP	Sum of V	Sum of IFC	Sum of Percentage Complete
5	26	12	0	12	0	0	2.307692308
6	28	6	3	3	0	0	1.964285714
7	30	1	0	0	0	1	3.333333333
8	31	36	17	15	4	0	1.908602151
9	32	54	8	19	0	27	2.453703704
10	33	3	1	2	0	0	1.717171717
11	41	26	21	0	0	5	1.454033771
12	42	1	1	0	0	0	1.19047619
13	47	4	4	0	0	0	1.063829787
14	51	48	48	0	0	0	0.980392157
15	58	7	7	0	0	0	0.862068966
16	Grand Total	198	110	51	4	33	1.609890833
Sheet1