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

  • Thread starter Thread starter Mike C
  • Start date Start date
M

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.
 
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
 
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.
 
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
 
similar question about calculated fields in pivot tables

I have a table which I've jammed together from disparate data sources to produce my final pivot table. In this pivot table, I've selected two fields (Manager and DataType) as column headers, and two fields (Shop Type and Grouped P&L) as row headers. DataType divides into two columns per manager; one for Actuals, and one for Plan. I now need to add a calculated field which divides the result of one by the other. Unfortunately, Excel's "Formulas" option isn't working for this, as it can't use ranges to point to the results of the DataType column header. I'm limited as to how much I can change the layout of this, due to being required to make it visually come close to the appearance of a previous version of the output.

I'd paste a sample of what the pivot table looks like, but the bulletin board software here loses track of the table formatting.

I've considered attempting to add a pre-calculated column to the raw data, but that won't work because, the calculation resulting in a proportion, they won't add correctly that way. I need to add the results before dividing the one by the other.

Any help is appreciated,

Mike Forbes
 
Back
Top