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
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"Mike C" <js2k111@yahoo.com> wrote in message
news:2831089f-7a4c-41c1-be44-03daede70a8f@k13g2000hse.googlegroups.com...
>
> 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.

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.

Mike C wrote:
> 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.


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

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

"Mike C" <js2k111@yahoo.com> wrote in message
news:2831089f-7a4c-41c1-be44-03daede70a8f@k13g2000hse.googlegroups.com...
>
> 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.