Pivot table formulas

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

Hello -
I am using XL2002. I have a pivot table and I was to include a simple
division formula. The table has a customer and sales info for 2001 and
2002. It is easy to have XL subtotal them, but I want the difference to be
shown, as opposed to the total of the 2 years. The pivot table field offers
avg., min., max., etc. but no difference option. MADDENING!
Thanks for any help you can provide.
Alan
 
Alan,
I must be missing something, then again I've always found pivot table help
dire.

So you've 3 columns ; Customer -- 2001 Sales -- 2002 Sales. If you
rightclick a data cell in the table and choose Field Settings, you should
get a PivotTable Field dialog. There should be an Options button to access a
"show data as" dropdown. One of it's options is "Difference From". By now I
thought I was on to something, but it wouldn't give me anything bar NAs.

So instead I tried (rightclick) Formulas -- Calculated Field. I defined a
name of From Sales2001, and gave it a formula of ='2002 Sales'-'2001 Sales'
(by clicking the options in the Fields listbox).

After clicking OK, this seemed to give me difference from 2001 (the original
table layout was Customer as Row, 2002 Sales as Data).

HTH,
Andy
 
Alan,

I tried recreating a simple pivot table and was able to
get the difference. Make sure you have Grand Totals on.
Click on a cell under the grand total row, right click
and go to field settings. Keep it on sum and click
options. Change 'Show Data As' from 'Normal'
to 'Difference From'. Select your year field and pick
eith year you want to see the difference from. Hope that
helps.

Tim
 
Hi Tim,
Click on a cell under the grand total row, right click
and go to field settings. Keep it on sum and click
Click to expand...

Not sure what you mean here -- under the GT row would be outside the actual
table? I couldn't get your method to work any way, plus any posts on Google
on this subject pointed OPs towards Formulas -- Calculated Field. If I could
have a squint at your file I'd be real interested.

TIA,
Andy
 
Hi everyone -
Thanks for the help - Tim, your solution worked, although once I changed the
field settings as suggested, the 2 listed dollar amounts disappeared
replaced by the difference only. This is the info I wanted, but I would
love to see the original amounts that it uses and I have tried every option
I can think of...
Thank you !
 
BUT WAIT!
I went back to the pivot table and dropped the sales fields back into the
report, so they showed up again.
a work around, but it works!
I shall now go and split the atom.
Thanks for your help !
Alan
 
Back
Top