Pivot table – custom Calculation

Topic

New Reply

Using Excel 2007:

Have a pivot table in a sheet. Column A has item numbers, column B has value data from November, Column C has value data from December. Doing the Pivot Table, when dragging the “extended cost” field to the values section – it populates the data for the months and then creates a new “grand total” column totalling the two month columns for each item #.

However – I want the Grand Total column to not show the total, but the difference between the two columns, from November to December. Trying to use the Difference From calculation doesn’t work – and it causes it to populate the difference in the December column.

Suggestions? Do I need to do a custom calculation and if so, how would I do it?

–*Rob

Reply | Quote

Replies

The simplest way to accomplish this is to code your calculation in another column, outside the bounds of the pivot table. For example, if your table occupies columns A, B, and C, and if row 3 contains the first detail line of your pivot table, you can calculate the difference between C3 and B3, simply by coding the formula: =C3-B3 in D3 (or any other unused column to the right of the the pivot table)

You can copy this fomula down the D column to accomplish this for every line of your pivot table.

The only drawback to this methd is that if you change the number of columns in the actual pivot table, you have to manually move the calculations. Since the formua lies outside the pivot table, and uses absolute row & column addresses, it does not change in sync with all the pivot table changes.

But for a simple, one time application, this is a quick solution. And it can be modified easily if the pivot table must change. I use this technique myself with some tables that are updated daily, but the number of columns rarely changes. Outside the dimensions of the pivot table the rest of the worksheet the table resides in is still available for use. And you can even reference the contents of the pivot table in this “outside” area (or in any other worksheet for that matter). you just need to be aware that changes to the pivot table can change, delete, or overlay the data and calculations in this “outside” area.

Reply | Quote

Hi robaustin – Is it possible to post a sample of what has been done so far. Sounds like a Calculated Field may be needed.

Reply | Quote

in another program I use, to do the subtraction in a pivot table you would use the following calculated field

=([Value] Where ([Month]=Nov)-[Value] Where ([Month]=Dec))

I’ve never tried it in excel, but would presume it would be similar.
Excel doesn’t have where so you might have to use an IF statement instead

capri

Reply | Quote