Pivot Table Data Sum/Count

[Guest]

I have multiple fields in the data portion of my pivot table. I have moved
the data field into the totals column to show these fields vertically.

The problem is that the fields have numbers or text. I want the pivot table
to sum the numbers and ignore the text as 0s. However, the software is
automatically putting fields with text and numbers as count and fields with
only numbers as sum.

I think I was able to do this before, but I can't remember what option I
clicked or unclicked.

Any assistance is appreciated. Thanks in advance.

 

[Roger Govier]

Hi

Right click on the PT and select PT wizard > Layout > Double click the
field label and select Sum

 

[Guest]

Ok, please forget the above message. I figured that out. My real question
is, is there a way to use the ceiling/floor functions on the totals in a
pivot table?

For example, I often use the formula: Ceiling(Floor(number, 1),5)
However, I don't see where to enter a formula such as this when using the
pivot table.

Again, assistance is appreciated. Thanks!

 

[Roger Govier]

Hi

Bring up the PT Toolbar.
From the dropdown select Formulas>Calculated Field
Give the new field a suitable title and in the formula pane enter your
formula
=Ceiling(Floor(number, 1),5) replacing number with the name of the
filed already included in the PT that you are trying to round in this
way.

 

[Guest]

Is there a way to get it to show without it being another column?

 

[Roger Govier]

Only by making the calculation as another column in the source data, and
then just including that column in the PT.

 

[Guest]

Hmmm... then do you have any other suggestions of ways I might go about
getting this information?

The problem is that I need to sum the group, then perform this action. I
don't think this will work for that. Plus, I have eight columns do this on
for each group.