Calculations in Pivot Table.

[Guest]

Folks,
I wanted to know if there is any way that I can divide my Pivot Table data
by 1000. In other words,

Data
+/- lbs +/-Sales
Product
Canine Dry 1,100,000 10,500,000
Feline Dry 500,000 6,500,000

This is a representation of the Pivot Table. I now need to divide the
numbers by 1,000 so that Canine Dry +/- Sales becomes 10,500.

In an Excel worksheet this is easy to do (Copy 1,000 and Edit Special divide
by 1,000 over your data range). I do not want to touch my Original Pivot
Data source.

Thanks for your help.

Shams.

 

[Sune Fibaek]

Folks,
I wanted to know if there is any way that I can divide my Pivot Table data
by 1000. In other words,

In the Pivot Table tool bar find Formulas (translated from mind, working
on a non-english version just now) > Calculated field... Divide your old
field by 1000: The formula should read something along the lines of
=Field/1000.

This will insert a new data item into the pivot table. Now just remove
the old field and you should now have all old values in thousands
without having altered the data range.

HTH.

/Sune

 

[S Davis]

I believe that you can click in the various Pivot columns, go to the
Insert menu, and select function, allowing you to enter formulas to
affect the displayed data. But I havent used it more than once myself
so Im unaware of its limitations or if it will work for you.

-Sean

 

[S Davis]

Sorry yes, this is what I meant. You can also reach this by clicking
in the insert menu and selecting 'Calculated Item' (which is what I
meant to type, but the brain is fuzzy)

 

[Lori]

If you just want to display the data as divided by 1000 you could use a
custom number format for the field: e.g. '#,' or '#,.000'.

If you choose this for the whole field it should retain the formatting
when pivotted.