Pivot Table - divide two results
 

I have data setup as follows in a Pivot table

customer Data Jan Grand Total
Sum of Sell
Sum of Gross Profit
Count of Inv
% of total Sales

I want to add another Data output that takes the Sum of Gross Profit/Sum of
Sell for each customer in this pivot table.

Does anyone know how to do this?

Thank you ,

PETE

You can add a calculated field to the pivot table:

Select a cell in the pivot table
On the Pivot Table toolbar, choose PivotTableFormulasCalculated Field
Type a name for the Field
IN the Formula box, type an equal sign
In the list of fields, double-click on Gross Profit
Type a division operator ( / )
In the list of fields, double-click on Sell
Click OK

Pete Petersen wrote:
I have data setup as follows in a Pivot table

customer Data Jan Grand Total
Sum of Sell
Sum of Gross Profit
Count of Inv
% of total Sales

I want to add another Data output that takes the Sum of Gross Profit/Sum of
Sell for each customer in this pivot table.

Does anyone know how to do this?

Thank you ,

PETE


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thank you so much for this help....

"Debra Dalgleish" wrote:

You can add a calculated field to the pivot table:

Select a cell in the pivot table
On the Pivot Table toolbar, choose PivotTableFormulasCalculated Field
Type a name for the Field
IN the Formula box, type an equal sign
In the list of fields, double-click on Gross Profit
Type a division operator ( / )
In the list of fields, double-click on Sell
Click OK

Pete Petersen wrote:
I have data setup as follows in a Pivot table

customer Data Jan Grand Total
Sum of Sell
Sum of Gross Profit
Count of Inv
% of total Sales

I want to add another Data output that takes the Sum of Gross Profit/Sum of
Sell for each customer in this pivot table.

Does anyone know how to do this?

Thank you ,

PETE


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I tried this with something similar and I got #DIV-0 for all the fields. Do
you have any idea why that would be?

"Debra Dalgleish" wrote:

You can add a calculated field to the pivot table:

Select a cell in the pivot table
On the Pivot Table toolbar, choose PivotTableFormulasCalculated Field
Type a name for the Field
IN the Formula box, type an equal sign
In the list of fields, double-click on Gross Profit
Type a division operator ( / )
In the list of fields, double-click on Sell
Click OK

Pete Petersen wrote:
I have data setup as follows in a Pivot table

customer Data Jan Grand Total
Sum of Sell
Sum of Gross Profit
Count of Inv
% of total Sales

I want to add another Data output that takes the Sum of Gross Profit/Sum of
Sell for each customer in this pivot table.

Does anyone know how to do this?

Thank you ,

PETE


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Were both fields numeric?

Sunryzz wrote:
I tried this with something similar and I got #DIV-0 for all the fields. Do
you have any idea why that would be?

"Debra Dalgleish" wrote:


You can add a calculated field to the pivot table:

Select a cell in the pivot table
On the Pivot Table toolbar, choose PivotTableFormulasCalculated Field
Type a name for the Field
IN the Formula box, type an equal sign
In the list of fields, double-click on Gross Profit
Type a division operator ( / )
In the list of fields, double-click on Sell
Click OK

Pete Petersen wrote:

I have data setup as follows in a Pivot table

customer Data Jan Grand Total
Sum of Sell
Sum of Gross Profit
Count of Inv
% of total Sales

I want to add another Data output that takes the Sum of Gross Profit/Sum of
Sell for each customer in this pivot table.

Does anyone know how to do this?

Thank you ,

PETE


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I think that was my problem. Thank you very much! Now, I just have one more
question. Can you tell me if it's possible to show this calculation only in
the total field for one of my rows instead of in every row?

"Debra Dalgleish" wrote:

Were both fields numeric?

Sunryzz wrote:
I tried this with something similar and I got #DIV-0 for all the fields. Do
you have any idea why that would be?

"Debra Dalgleish" wrote:


You can add a calculated field to the pivot table:

Select a cell in the pivot table
On the Pivot Table toolbar, choose PivotTableFormulasCalculated Field
Type a name for the Field
IN the Formula box, type an equal sign
In the list of fields, double-click on Gross Profit
Type a division operator ( / )
In the list of fields, double-click on Sell
Click OK

Pete Petersen wrote:

I have data setup as follows in a Pivot table

customer Data Jan Grand Total
Sum of Sell
Sum of Gross Profit
Count of Inv
% of total Sales

I want to add another Data output that takes the Sum of Gross Profit/Sum of
Sell for each customer in this pivot table.

Does anyone know how to do this?

Thank you ,

PETE


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Pivot Table - divide two results
 

It seems it only allows you to create formulas from data in the source data.
I want to create formulas using the results of a pivot table. I have a column
that gives me the count of employees and another that gives me the average
lenght of stay in the company. I want to divide the count by the lenght of
stay....these are calculated fields by the pivot table and not available in
the master data. When I try to go to the calculate function those two new
fields are not an option? Hope this is clear

Carolina

"Debra Dalgleish" wrote:

Were both fields numeric?

Sunryzz wrote:
I tried this with something similar and I got #DIV-0 for all the fields. Do
you have any idea why that would be?

"Debra Dalgleish" wrote:


You can add a calculated field to the pivot table:

Select a cell in the pivot table
On the Pivot Table toolbar, choose PivotTableFormulasCalculated Field
Type a name for the Field
IN the Formula box, type an equal sign
In the list of fields, double-click on Gross Profit
Type a division operator ( / )
In the list of fields, double-click on Sell
Click OK

Pete Petersen wrote:

I have data setup as follows in a Pivot table

customer Data Jan Grand Total
Sum of Sell
Sum of Gross Profit
Count of Inv
% of total Sales

I want to add another Data output that takes the Sum of Gross Profit/Sum of
Sell for each customer in this pivot table.

Does anyone know how to do this?

Thank you ,

PETE


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html