Calculated Column in Pivot Table

Topic

New Reply

How do I subtract two columns of data in a pivot table?

The column name is LE and the data is Sum of Amount…

Thanks.

Reply | Quote

Replies

You did not mention the Excel version. FOr 2003 and below: click anywhere in the pivot table. Then on the pivot table toolbar, select PivotTable, Formulas, Calculated field. That dialog speaks for itself I think.

Reply | Quote

I think I want to calculate an item because those fields don’t come up as options. So, don’t I want to do this instead of calculate a field??

You did not mention the Excel version. FOr 2003 and below: click anywhere in the pivot table. Then on the pivot table toolbar, select PivotTable, Formulas, Calculated field. That dialog speaks for itself I think.

Reply | Quote

You can’t use the data fields of the pivot table in a calculated field.

It might be helpful of you posted (a stripped down copy of) your workbook, with an indication of what you want.

Reply | Quote

Ok. Here is a picture. I want to subtract the March LE data from the December LE data – column f from column e.

You can’t use the data fields of the pivot table in a calculated field.

It might be helpful of you posted (a stripped down copy of) your workbook, with an indication of what you want.

Reply | Quote

Note: I want to add the column w/the subtraction and title it variance…

You can’t use the data fields of the pivot table in a calculated field.

It might be helpful of you posted (a stripped down copy of) your workbook, with an indication of what you want.

Reply | Quote

Click in the pivot table.
Start the Pivot Table Wizard.
Click Layout…
Add the Amount field to the Data area, since there is already an instance of Amount there, the new one will be called Sumof Amount2.
Double-click the Sum of Amount2 button.
Change the name if you like, then click Options>>.
From the “Show data as” dropdown, select “Difference with”.
Select LE in the Base Field list.
Select (previous) in the Base Item list.
Click OK, OK, Finish.

Reply | Quote

Hans, this didn’t work… I am wanting to add an extra column to calculate this…

Does this change your answer???

Mitch

Click in the pivot table.
Start the Pivot Table Wizard.
Click Layout…
Add the Amount field to the Data area, since there is already an instance of Amount there, the new one will be called Sumof Amount2.
Double-click the Sum of Amount2 button.
Change the name if you like, then click Options>>.
From the “Show data as” dropdown, select “Difference with”.
Select LE in the Base Field list.
Select (previous) in the Base Item list.
Click OK, OK, Finish.

Reply | Quote

Does this change your answer???

Yes, in the sense that it can’t be done in the pivot table as far as I know. You could add formulas to the right of the pivot table to calculate the difference, but they wouldn’t be updated correctly if you changed the layout of the pivot table.

Reply | Quote

Hans, it looks like I asked this same thing back on July 25, 2008. Below is your reply. I am trying a similar approach to this table, but it locks up my PC taking up all the CPU. I let it run for an hour and it was finished but didn’t show a result…

“Sorry, I interpreted your “I have two sets of columnar data” in a different way than you intended it. What you need is a calculated item instead of a calculated formula.
– Click in one of the values of version.
– From the Pivot Table toolbar, select Pivot Table | Formulas | Calculated Item…
– Enter a name for the formula in the box at the top.
– Enter the following formula in the formula box:

=versions[2008 OP]-versions[Sept LE]

– Make sure that you use the names exactly as they are in the data.
– Click Add, then OK. ”

Yes, in the sense that it can’t be done in the pivot table as far as I know. You could add formulas to the right of the pivot table to calculate the difference, but they wouldn’t be updated correctly if you changed the layout of the pivot table.

Reply | Quote

I don’t know enough about your workbook.

Reply | Quote

Attached is the file I’d like to do the math on…

Mitch

I don’t know enough about your workbook.

Reply | Quote

Click on Jan LE or Mar LE (or in one of the row fields)
Select Pivot Table | Formulas | Calculated Item from the Pivot Table toolbar.
Type a name for the field, e.g. Variance.
Edit the formula so that it consists only of =
Select Jan LE item in the list ot items, then click Add Item.
Type – after it in the formula.
Select Mar LE item in the list ot items, then click Add Item.
The formula should now read =’Jan LE’-‘Mar LE’
Click Add, then click OK

BTW you can reduce the size of the workbook significantly by basing it on the data table only instead of on entire columns.

See the attached version.

Reply | Quote

Hans, I was able to get it to work also on the smaller subset of data I posted. When I try to do it on the full data set, it never finishes and takes up 100% of the CPU for hours…

I see your comment below, “BTW you can reduce the size of the workbook significantly by basing it on the data table only instead of on entire columns.” Maybe this would fix the problem with the full data set.

How do I do what you are referring to?

Mitch

Click on Jan LE or Mar LE (or in one of the row fields)
Select Pivot Table | Formulas | Calculated Item from the Pivot Table toolbar.
Type a name for the field, e.g. Variance.
Edit the formula so that it consists only of =
Select Jan LE item in the list ot items, then click Add Item.
Type – after it in the formula.
Select Mar LE item in the list ot items, then click Add Item.
The formula should now read =’Jan LE’-‘Mar LE’
Click Add, then click OK

BTW you can reduce the size of the workbook significantly by basing it on the data table only instead of on entire columns.

See the attached version.

Reply | Quote

How do I do what you are referring to?

Click anywhere in the pivot table.
Select Pivot Table | Pivot Table Wizard from the Pivot Table toolbar.
Click Back until you see the range the pivot table is based on. This range consists of entire columns. Clear this, and point to the data range.
Finally, click Finish.

Reply | Quote