how to use pivot data in formula or fuctions?

S

summerhayashi

Hi,
I've extracted out some data using pivot, and have results as shown below
example

# country A B C
1 USA 1 0 3
2 JPN 0 0 1

Next I'd like to get the max value of column A, B and C and so I make use of
the function =max(A1,B1,C1) (note : the values are captured by selecting the
cells which A1, B1 and C1 will be captured.
But when I drag to the 2nd Row, to copy the formula/fucntion to the next
row, the formula still capture A1,B1,C1 instead of A2,B2,C2

Any idea how to by pass this instead of copy the results into another column
as static info bec I am trying to automate and avoid using static way of
doing things.
 
A

AFSSkier

Pivot table calculations should be done with the pivot table formula wizard
(PivotTable Tools, Options, Formulas, Calculated Field). Name the new field,
In the Formula: =max('yourfieldA','yourfieldB','yourfieldC') or in your
example =max('A','B','C').

The formula will copy it's self down. Also as the pivot table grows &
shrinks, so will your formula cells.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top