View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Nick Hodge[_2_] Nick Hodge[_2_] is offline
external usenet poster
 
Posts: 185
Default Excel 2002 Pivot Table: Can I customize formulas ?

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
DTHIS
web:
www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog


"Mr. Low" wrote in message
...
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