# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] Pivot Table - divide two results

## Pete Petersen

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

You can add a calculated field to the pivot table:

Select a cell in the pivot table
On the Pivot Table toolbar, choose PivotTable>Formulas>Calculated 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

----------


## Pete Petersen

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 PivotTable>Formulas>Calculated 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
>
>

----------


## Sunryzz

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 PivotTable>Formulas>Calculated 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

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 PivotTable>Formulas>Calculated 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

----------


## Sunryzz

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 PivotTable>Formulas>Calculated 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
>
>

----------

