Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculations in Pivot Table.
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculations in Pivot Table.
Shams wrote:
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculations in Pivot Table.
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 Shams wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculations in Pivot Table.
:) 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) Sune Fibaek wrote: Shams wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculations in Pivot Table.
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. Shams wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to increase calculations speed in pivot table with calculated fields & items | Excel Discussion (Misc queries) | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Creating Calculations Within a Pivot Table Field | Excel Discussion (Misc queries) | |||
Setting up "Year to Date" Calculations in a Pivot Table | Excel Worksheet Functions | |||
pivot table calculations | Excel Discussion (Misc queries) |