Thanks:  0
Likes:  0

1. I would like to modify the SUM formula in my pivot table to round to the dollar and say -SUM since my souce data is all the opposite sign of how I want it to appear on my pivot table. Is the only way to accomplish this with a calculated field or is there some way to define exactly the SUM formula I want to use in the table?

2. You could add a column to the original data using the absolute formula this will create all positive numbers than use that coulmn in your pivot

=Abs(a5)

3. Using the data...
******>
 Fruit Qty Apples \$10.10 Apples \$15.25 Oranges \$20.50 Oranges \$25.80 Oranges \$30.00

...a Calculated field using the formula...

=-ROUND(Qty,0)

...produced...

******>
 Data Fruit Sum of Qty Sum of -Qty Apples 25.35 -25 Oranges 76.3 -76 Grand Total 101.65 -102

[ This Message was edited by: Mark W. on 2002-04-09 14:24 ]

4. Ok, maybe I am missing something but wouldnt it be easier to double click the data field in layout, then number and then to 0 decimal places. Or did you want both numbers??

5. On 2002-04-09 14:29, EdE wrote:
Ok, maybe I am missing something but wouldnt it be easier to double click the data field in layout, then number and then to 0 decimal places. Or did you want both numbers??
Formatting alone doesn't alter the precision of underlying values.

Also, a Calculated Field (in this case =ROUND(QTY,0)-QTY) allows you to produce PivotTables with derived results...

******>
 Sum of Rounded Diff Fruit Total Apples (\$0.35) Oranges (\$0.30) Grand Total \$0.35

[ This Message was edited by: Mark W. on 2002-04-09 15:23 ]

6. Thanks for the info, but it wasn't quite what I was looking for. Here is a sample of my data set

Acct Dept Amount
3000 SiteA -5000
3005 SiteB -3500
4000 SiteA 2000
4005 SiteB 1000
4500 SiteA 700
4505 SiteB 300
4600 SiteA 200
4605 SiteB 75
4750 SiteA 45

What I'm looking for is to have rows for the Acct's, columns for the Dept's and then to have the amounts rounded to zero and reverse the sign. I hope this helps.

7. On 2002-04-10 08:52, 2077delta wrote:
Thanks for the info, but it wasn't quite what I was looking for. Here is a sample of my data set

Acct Dept Amount
3000 SiteA -5000
3005 SiteB -3500
4000 SiteA 2000
4005 SiteB 1000
4500 SiteA 700
4505 SiteB 300
4600 SiteA 200
4605 SiteB 75
4750 SiteA 45

What I'm looking for is to have rows for the Acct's, columns for the Dept's and then to have the amounts rounded to zero and reverse the sign. I hope this helps.
What do you mean by "have the amounts rounded to zero..."?

8. I must apologize, my sample data was all rounded to zero decimals, but the actual data is all in dollar and cents. I would like to round all the dollar and cents to just whole dollars.

9. On 2002-04-10 09:29, 2077delta wrote:
I must apologize, my sample data was all rounded to zero decimals, but the actual data is all in dollar and cents. I would like to round all the dollar and cents to just whole dollars.
Okay, this PivotTable...

******>
 Sum of Amount2 Dept Acct Admin SiteA SiteB Grand Total 3000 5000 5000 3005 3500 3500 3010 50 50 3300 25 25 4000 -2000 -2000 4005 -1000 -1000 4500 -700 -700 4505 -300 -300 4600 -200 -200 4605 -75 -75 4750 -45 -45 5000 -1000 -1000 Grand Total -925 2055 2125 3255

...uses a Calculated Field, 'Amount2', defined as...

=-ROUND(Amount,0)

[ This Message was edited by: Mark W. on 2002-04-10 10:04 ]

10. Thank you. Your answer is exactly what I was looking for. One of those "it was so simple I didn't see" it type of things.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•