Excel Pivot Table Row Subtotal

[ddecoste]

Everybody,

I would like to change the Type subtotal to a difference between the
Actual and Forcast. I have the field Part Number and Type in the row
section, Month in the column selection and the sum of qty in the data
selection. Can the Pivot table do what I am looking for.

I have a pivot table similar to this:
January | February
Part Number | Type | --------------------
123456 | Actual | 120 | 240
| Forcast | 150 | 230
123456 Total | 270 | 470
234567 | Actual | 100 | 500
| Forcast | 90 | 560
234567 Total | 190 | 1060


I would like to change it to something like this:

January | February
Part Number | Type | --------------------
123456 | Actual | 120 | 240
| Forcast | 150 | 230
123456 Difference | 30 | -10
234567 | Actual | 100 | 500
| Forcast | 90 | 560
234567 Difference | -10 | 60


Thank you for your help.

Darren

 

[Dave Peterson]

Debra Dalgleish has some techniques for do custom calculations at:
http://contextures.com/xlPivot10.html

(but I couldn't get them to work for me--but you (or Debra!) may have better
luck.)

I think I'd cheat.

I'd make the actual quantities positive and the forecast (with an E) negative
(or vice versa).

Maybe just use an additional column with a formula like:
=if(b2="Actual",c2,-c2)

And drag down.