PivotTable-How to take difference between groupings of data
Posted by Jim on January 11, 2001 1:54 PM
I am trying to create an Income Statement PivotTable. In the row field of my PivotTable, I have several groupings of General Ledger account numbers. A group called 'Net Sales' is made up of 100 Sales accounts. A group called 'Cost of Sales' is made up of 80 Cost of Goods Sold accounts. The next grouping starts at the next line. I need to insert a line before that next grouping called 'Gross Profit' that subtracts the total of 'Cost of Sales' group from the total of 'Net Sales' group. I am having problems. Anyone know how to do this or a work around. Thanks.
Posted by Mark W. on January 11, 2001 3:00 PM
If cells A1:C4 contain {"Fruit","Sales","Cost";"Apples",100,5;"Oranges",200,10;"Lemons",300,15}.
1. Create a Pivot table with "Sales" and "Cost"
in the Data Area.
2. Double-click the "Sales" field and rename it to
"Goods Sold"
3. Double-click the "Cost" field and rename it to
"Cost of Goods".
4. After finishing the PivotTable click on any of
the row item.
5. Choose the Insert Calculated Field... menu
command.
6. Name the field "Net Sales", enter the formula,
=Sales-Cost and press the Add and OK buttons.
7. Select the cell containing "Sum of Net Sales"
and enter "Net Sales ".
Posted by Jim on January 16, 2001 7:21 AM
Mark - Your response made me realize that I cannot add a calculated item while my pivot table field is grouped. (It gave me a message telling me so). Therefore I ungrouped all my data and added 16 calculation items equaling zero. I then regrouped my account numbers. When updating my calc. items, I tried to take the difference between 'Net Sales' and 'Cost of Sales' but the message came up "Group item names are not supported in calculated item formulas". I guess I am back at square one.
