If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pivot table formulas
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 |
#2
|
|||
|
|||
Pivot table formulas
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 |
#3
|
|||
|
|||
Pivot table formulas
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 -----Original Message----- 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 . |
#4
|
|||
|
|||
Pivot table formulas
Hi Tim,
Click on a cell under the grand total row, right click and go to field settings. Keep it on sum and click 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 |
#5
|
|||
|
|||
Pivot table formulas
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 ! "Andy Brown" wrote in message ... Hi Tim, Click on a cell under the grand total row, right click and go to field settings. Keep it on sum and click 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 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 |
#6
|
|||
|
|||
Pivot table formulas
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 "Andy Brown" wrote in message ... Hi Tim, Click on a cell under the grand total row, right click and go to field settings. Keep it on sum and click 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 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 |
Thread Tools | |
Display Modes | |
|
|