Excel 2002 Pivot Table: Can I customize formulas ?

[Mr. Low]

Dear Sir,

At present I know that the last column of the Pivot Table is the Sum of the
previous column by default.

Alternatively the user can select count , variance etc instead of the sum
from the menu to get the answer.

If I would like to use the Pivot Table in the following way, may I know if
it is possible to input the formula for the last column as illustrated below?

Example 1

Data Data
Account Source A Source B Difference
K123 2000 1800 200
P214
T114
Difference = Source A - Source B



Example 2

Debtor 30 days 60 days Target Collection
P587 2000 5000 3750
C321
U206
Target debt collection = (25% X 30 days) + (65% X 60days)


Example 3

Product Plant A Plant B Plant C Plant A %
Group Unit Unit Unit
T588 3500 2500 4600 33%
Q249
X125
Plant A% = Plant A /(Plant A+B+C) X 100%


At present I copy the Pivot Table and past it to another worksheet and
manipulate the figure manually. I do not have any problem with that, however
this method does not allow the data to be automatically updated by refresh
when the raw data are updated.


Thanks

Low



A36B58K641

 

[Nick Hodge]

Mr Low

You can enter most functions as a calculated field in a pivot table. It sits
under the drop down on the pivot table toolbar under formulas...calculated
field.

You then use the field names in your formula and as the table is refreshed
the formula acts like a new field

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog

 

[Mr. Low]

Hello Nick,

Yes, I could find formulas > Calculated fied > Insert field.

However I do not know how to go about to input the formulas.

Can you show me step by step base on the one of the example I show earlier
on ?

Thanks

Low

 

[Nick Hodge]

Low

In the dialog you get when you take the options I gave you, you should have
in the dropdown a list of the fields in the pivot table (in your top example
SourceA and SourceB). In the box just enter = and then double click SourceA
and then enter a '-' and then double click SourceB (You should now have
=SourceA-SourceB) give it a name and click 'Add'

Now you can use this new calculated field like any other field

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog