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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike C - 17 May 2008 16:54 GMT

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.

Reply to this Message

Nick Hodge - 17 May 2008 18:10 GMT

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)

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk

> I have a pivot table that includes the sum of sales (a data field) and
> the count of sales (a data field) by product.
[quoted text clipped - 16 lines]
>
> Thanks for any suggestions.

Reply to this Message

Debra Dalgleish - 17 May 2008 18:31 GMT

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.

> I have a pivot table that includes the sum of sales (a data field) and
> the count of sales (a data field) by product.
[quoted text clipped - 16 lines]
>
> Thanks for any suggestions.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Shane Devenshire - 18 May 2008 01:31 GMT

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

> I have a pivot table that includes the sum of sales (a data field) and
> the count of sales (a data field) by product.
[quoted text clipped - 16 lines]
>
> Thanks for any suggestions.

Reply to this Message