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

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


" wrote:

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

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

On 27 Apr, 16:58, Eduardo wrote:
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



" wrote:
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- Hide quoted text -

- Show quoted text -

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

" wrote:

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

On 27 Apr, 16:58, Eduardo wrote:
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



" wrote:
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- Hide quoted text -

- Show quoted text -

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

" wrote:

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

On 27 Apr, 16:58, Eduardo wrote:
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



" wrote:
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- Hide quoted text -

- Show quoted text -