Crazy Pivot Table Calculated Field Question

[ammdumas]

Howdy,

This is a really hard question to formulate so I've posted the sheet below (using Excel 2007 and XP). I need to get percentage results based on a pivot table. So far I can only figure out how to do this outside of the pivot table and I was wondering if it's possible to create a calculated field so i don;t have to adjust/copy the formula every time I refresh the table.

In short I need to figure out percentage complete by area (the 4 digit code to the left), based on STATUS. The 4 different statuses (milestones) have a percentage associated to them (PREL=50%, ENGD=65%, etc.). So I have a formula with those percentages for each STATUS (divided by the total for that area) and then I add them all up to give me a percentage complete for a particular area.

A thru F is the pivot table...Thoughts?

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
3	Sum of TVol	Column Labels					% @ IFC	% @ PREL	% @ REVIEWED	% @ ENGD	TOTAL
4	Row Labels	IFC	PREL	REVIEWED	ENGD	Grand Total
5			31.67			31.67	0.00	50.00	0.00	0.00	50.00
6	3120	0.21	13.03		0.00	13.25	1.60	49.19	0.00	0.01	50.80
7	3311		20.26			20.26	0.00	50.00	0.00	0.00	50.00
8	3320		4.37			4.37	0.00	50.00	0.00	0.00	50.00
9	3321		3.91			3.91	0.00	50.00	0.00	0.00	50.00
10	3331		15.62			15.62	0.00	50.00	0.00	0.00	50.00
11	3333		4.84			4.84	0.00	50.00	0.00	0.00	50.00
12	3342		0.97			0.97	0.00	50.00	0.00	0.00	50.00
13	3411		121.13			121.13	0.00	50.00	0.00	0.00	50.00
14	3421		218.30			218.30	0.00	50.00	0.00	0.00	50.00
15	3512		9.24			9.24	0.00	50.00	0.00	0.00	50.00
16	3514		2.92			2.92	0.00	50.00	0.00	0.00	50.00
17	3542		30.51			30.51	0.00	50.00	0.00	0.00	50.00
18	3710	32.58				32.58	100.00	0.00	0.00	0.00	100.00
19	3730			23.73		23.73	0.00	0.00	85.00	0.00	85.00
20	3740			17.07		17.07	0.00	0.00	85.00	0.00	85.00
21	6610		5.23			5.23	0.00	50.00	0.00	0.00	50.00
22	6710		21.22			21.22	0.00	50.00	0.00	0.00	50.00
23	STEL		4.22			4.22	0.00	50.00	0.00	0.00	50.00
24	Grand Total	32.79	507.45	40.80	0.00	581.04
PIVOT

Cell Formulas
Range	Formula
G5	=B5/$F5*100
G6	=B6/$F6*100
G7	=B7/$F7*100
G8	=B8/$F8*100
G9	=B9/$F9*100
G10	=B10/$F10*100
G11	=B11/$F11*100
G12	=B12/$F12*100
G13	=B13/$F13*100
G14	=B14/$F14*100
G15	=B15/$F15*100
G16	=B16/$F16*100
G17	=B17/$F17*100
G18	=B18/$F18*100
G19	=B19/$F19*100
G20	=B20/$F20*100
G21	=B21/$F21*100
G22	=B22/$F22*100
G23	=B23/$F23*100
H5	=C5/$F5*50
H6	=C6/$F6*50
H7	=C7/$F7*50
H8	=C8/$F8*50
H9	=C9/$F9*50
H10	=C10/$F10*50
H11	=C11/$F11*50
H12	=C12/$F12*50
H13	=C13/$F13*50
H14	=C14/$F14*50
H15	=C15/$F15*50
H16	=C16/$F16*50
H17	=C17/$F17*50
H18	=C18/$F18*50
H19	=C19/$F19*50
H20	=C20/$F20*50
H21	=C21/$F21*50
H22	=C22/$F22*50
H23	=C23/$F23*50
I5	=D5/$F5*85
I6	=D6/$F6*85
I7	=D7/$F7*85
I8	=D8/$F8*85
I9	=D9/$F9*85
I10	=D10/$F10*85
I11	=D11/$F11*85
I12	=D12/$F12*85
I13	=D13/$F13*85
I14	=D14/$F14*85
I15	=D15/$F15*85
I16	=D16/$F16*85
I17	=D17/$F17*85
I18	=D18/$F18*85
I19	=D19/$F19*85
I20	=D20/$F20*85
I21	=D21/$F21*85
I22	=D22/$F22*85
I23	=D23/$F23*85
J5	=E5/$F5*65
J6	=E6/$F6*65
J7	=E7/$F7*65
J8	=E8/$F8*65
J9	=E9/$F9*65
J10	=E10/$F10*65
J11	=E11/$F11*65
J12	=E12/$F12*65
J13	=E13/$F13*65
J14	=E14/$F14*65
J15	=E15/$F15*65
J16	=E16/$F16*65
J17	=E17/$F17*65
J18	=E18/$F18*65
J19	=E19/$F19*65
J20	=E20/$F20*65
J21	=E21/$F21*65
J22	=E22/$F22*65
J23	=E23/$F23*65
K5	=SUM(G5:J5)
K6	=SUM(G6:J6)
K7	=SUM(G7:J7)
K8	=SUM(G8:J8)
K9	=SUM(G9:J9)
K10	=SUM(G10:J10)
K11	=SUM(G11:J11)
K12	=SUM(G12:J12)
K13	=SUM(G13:J13)
K14	=SUM(G14:J14)
K15	=SUM(G15:J15)
K16	=SUM(G16:J16)
K17	=SUM(G17:J17)
K18	=SUM(G18:J18)
K19	=SUM(G19:J19)
K20	=SUM(G20:J20)
K21	=SUM(G21:J21)
K22	=SUM(G22:J22)
K23	=SUM(G23:J23)

 

[cyrilbrd]

Hi ammdumas,

I most likely misunderstood your question...
If you want to have % @ IFC % @ PREL % @ REVIEWED % @ ENGD TOTAL incorporated in your Pivot, use calculated fields as follows:
% @ IFC:

=IFC/(IFC+PREL+REVIEWED+ENGD)*100

% @ PREL

=PREL/(IFC+PREL+REVIEWED+ENGD)*50

% @ REVIEWED

=REVIEWED/(IFC+PREL+REVIEWED+ENGD)*85

% @ ENGD

=ENGD/(IFC+PREL+REVIEWED+ENGD)*65

TOTAL

='%IFC'+ '% @ PREL'+ '% @ Reviewed'+ '% @ Eng'

Is this what you wished to do?
urlink

 

[ammdumas]

Cool, at home right now but I'll give this a try when I get back to work.

 

[cyrilbrd]

ok, let us know if it works for you.

 

[ammdumas]

Hey. Ya, this won't work. There's one column in the core table called STATUS and it's in that table where you see data like PREL, IFC, etc. etc. They are not column unto themselves and therefore can't be called as items in a calculation field.

Have a look at this post. I tried to simplify my question a bit...

http://www.mrexcel.com/forum/showthread.php?t=620881