software

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, SimonIf 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.

wrote:
gt; I have several pivot table reports - in some the datwa is shown as $
gt; format, in others the data is shown in Count format.
gt;
gt; I want to create a new table which simply divides the $ data from one
gt; table by the count data from another. However, when I use the pivot
gt; table formula function to do this I can select the fields OK but I get
gt; an error result. The formula seems to have trouble dividing a $ format
gt; by a Count format. Does anyone know how to get around this?
gt;
gt; Thanks, Simon
gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlDebra

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, SimonYou 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.
wrote:
gt; Debra
gt;
gt; Thank you for your response. The field I'm counting is not a text
gt; field, it is numeric. That's why I don't understand why I'm unable to
gt; create a pivot table formula to divide one field by another. Surely I
gt; should be able to do this within the pivot table.
gt;
gt; Thanks, Simon
gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlDebra

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, SimonDebra

Entering a new col with 1 worked a treat.

Thank you so much for your help.

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

wrote:
gt; Debra
gt;
gt; Entering a new col with 1 worked a treat.
gt;
gt; Thank you so much for your help.
gt;
gt; Simon
gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.html