When you create a calculated field, the Sum each field is used in the
calculation, no matter which function you're using for that field in the
pivot table.

So, even though the pivot table is showing the Max of Messages, its Sum
is used in the Messages/Replies calculation.

You could create a calculation outside of the pivot table, using the
GETPIVOTDATA function to extract the numbers that you need.

http://www.contextures.com/xlPivot06.html


cw wrote:
Hi,
Pivot table problems.

http://i39.tinypic.com/2ep7bwj.jpg


I'm selling by Mail order.
I send mesages.
I receive replies.
Replies give rise to sales.
Sales have dates.
Dates have years.

I having trouble getting the correct Reply Rate = (Replies / messages).
I'm getting the Bad (in red) instead of what I need, the OK (in green).

Messages is fixed (I sent only one batch of messages).
So instead of displaying the Sum of messages I display the Max of messages.

So the Messages display OK in each year column and OK in the total.

Sales / replies gives average sale per reply.
Messages / replies should give the reply rate... but doesn't.

Any help appreciated.

CW




--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com