Pivot Table question: Creating a new field that calculates thedivision of one data field by another

[Mike C]

I have a pivot table that includes the sum of sales (a data field) and
the count of sales (a data field) by product.

It looks like this.
sum of sales Count of units
Product a 100 8
product b 45 6
Etc.

I am trying to create another field that divides the sum of sales by
the count of units for each product (i.e. in a fourth column.

Is there an easy way to do this (within the pivot table). I tried
using the calculated field option, but didn't seem to find the way to
do it.

Also once I have created a calculated field that I no longer want, is
there a way to delete it.

Thanks for any suggestions.

 

[Nick Hodge]

Mike

yes and yes. You do not give us version, but right clicking in the pivot
table should give you 'Formulas...' select calculated field and in the
dialog type a formula like you would normally

=Sales/Units

and click add. You may want to include an IF function if you have fields
with zero units to avoid #DIV0! errors.

if you want to delete it, slelect it via the same method and click delete.
(You can also just drag the new field off the grid so it will be available
later)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Debra Dalgleish]

A calculated field will always use the Sum of the selected fields, even
if you have used a different function in the pivot table, such as Count.

As a workaround, you could add a column to the source data table, with a
heading of UnitCount. Enter a 1 in each row.
Then, refresh the pivot table, and add the UnitCount field to the data
area, as Sum.
Use the UnitCount field in the calculated field, instead of the Units field.

 

[Shane Devenshire]

Hi Mike,

Here is another option which is most likely not applicable to the current
situation, but maybe...

If Count of Units = Count of Sales then you can add the field Sales again
and use the summary operation Average.

The problem here is that we can't see a sample of the source data and the
approach is dependent on that. In some cases a calculated field would be
appropriate, in others an additional field in the data area would be
appropriate, and in yet others the use of an average field. If you show us
some sample data and the desired results that would help immensely.

Cheers,
Shane Devenshire
Microsoft Excel MVP