Pivot Table with Percentages

[thewritings]

Hi all,

I am trying to work out the % of cost to sales.I have using a pivot
table to get the sales & cost dat and manually calculated the %
I know that Excel has pivot table % in the field settings- options but
they do not give me what I am after.

Is there a solution using pivot tables?



Genre Fiscal year Sum of Sales £ Sum of Cost £ %
CH V4/2003 48,607 (422) 0.87%
V4/2004 49,284 (785) 1.59%
V4/2005 52,816 (2,052) 3.89%
V4/2006 35,165 (722) 2.05%
V4/2007 47,812 (956) 2.00%
V4/2008 59,255 (934) 1.58%
CH Total 292,938 (5,871)
2.00%


Thanks
TW

 

[Eduardo]

Hi,
You are close to the solution
Just need to add a calculated field in the Pivot Table

Go to the PT, then under tools choose Formulas, Calculate field, choose the
name of the column , then choose cost, add enter / and then choose sales add,
click OK , a new column will be added, format that column as % with 2 decimals

If this was helpful please click yes, thanks

 

[thewritings]

Many Thanks Eduardo- this was very helpful.

But I cannot see this function - is it a new feature in Excel 2007? I
am currently using 2003.

TW

 

[Eduardo]

hi,
yes is 2007, however if nobody answer to this post before I get home I will
send the instructions since I have 2003 at home

 

[Eduardo]

Hi,
Once in the pivot table, with the field list open expand the arrow where it
says Pivot Table, formulas, Calculated field and the proceed as I told you
before