Pivot table formula

[simonvinson]

I have several pivot table reports - in some the datwa is shown as $
format, in others the data is shown in Count format.

I want to create a new table which simply divides the $ data from one
table by the count data from another. However, when I use the pivot
table formula function to do this I can select the fields OK but I get
an error result. The formula seems to have trouble dividing a $ format
by a Count format. Does anyone know how to get around this?

Thanks, Simon

 

[Debra Dalgleish]

If the field you're counting is a text field, the result will be a
#DIV/0! error. You could calculate the result outside of the pivot
table, instead of creating a calculated field in the pivot table.

 

[simonvinson]

Debra

Thank you for your response. The field I'm counting is not a text
field, it is numeric. That's why I don't understand why I'm unable to
create a pivot table formula to divide one field by another. Surely I
should be able to do this within the pivot table.

Thanks, Simon

 

[Debra Dalgleish]

You can use a calculated field to divide the Sum of one field by the Sum
of another. Even if the field is summarized by Count in the data area,
its Sum will be used in the calculated field, not the Count.

So, if you're trying to divide sum of FieldA by Count of FieldB, you
could do the calculation outside of the pivot table, referring to the
values in the pivot table.

Or, add a field to the source data that contains a 1 for each record.
Then, use a calculated field to divide FieldA by the Sum of this new field.

 

[simonvinson]

Debra

Thank so much for your suggestions - I like your idea of simply adding
1 for each record, so I'll give that a go.

Thanks, Simon

 

[simonvinson]

Debra

Entering a new col with 1 worked a treat.

Thank you so much for your help.

Simon

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.