Pivot table

2077delta

Active Member
Joined
Feb 17, 2002
Messages
250
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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)
 
Upvote 0
Using the data...<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td>Fruit</td><td>Qty</td></tr><tr><td>Apples</td><td>$10.10</td></tr><tr><td>Apples</td><td>$15.25</td></tr><tr><td>Oranges</td><td>$20.50</td></tr><tr><td>Oranges</td><td>$25.80</td></tr><tr><td>Oranges</td><td>$30.00</td></tr></table></body></html>

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

=-ROUND(Qty,0)

...produced...<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td></td><td>Data</td><td></td></tr><tr><td>Fruit</td><td>Sum of Qty</td><td>Sum of -Qty</td></tr><tr><td>Apples</td><td>25.35</td><td>-25</td></tr><tr><td>Oranges</td><td>76.3</td><td>-76</td></tr><tr><td>Grand Total</td><td>101.65</td><td>-102</td></tr></table>
 </body></html>
This message was edited by Mark W. on 2002-04-09 14:24
 
Upvote 0
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??
 
Upvote 0
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...<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td>Sum of Rounded Diff</td><td></td></tr><tr><td>Fruit</td><td>Total</td></tr><tr><td>Apples</td><td><font color="#FF0000">($0.35)</font></td></tr><tr><td>Oranges</td><td><font color="#FF0000">($0.30)</font></td></tr><tr><td>Grand Total</td><td>$0.35 </td></tr></table>
 </body></html>
This message was edited by Mark W. on 2002-04-09 15:23
 
Upvote 0
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
3010 Admin -50
3300 Admin -25
4000 SiteA 2000
4005 SiteB 1000
4500 SiteA 700
4505 SiteB 300
4600 SiteA 200
4605 SiteB 75
4750 SiteA 45
5000 Admin 1000

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.
 
Upvote 0
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
3010 Admin -50
3300 Admin -25
4000 SiteA 2000
4005 SiteB 1000
4500 SiteA 700
4505 SiteB 300
4600 SiteA 200
4605 SiteB 75
4750 SiteA 45
5000 Admin 1000

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..."?
 
Upvote 0
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.
 
Upvote 0
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...<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td>Sum of Amount2</td><td>Dept</td><td></td><td></td><td></td></tr><tr><td>Acct</td><td>Admin</td><td>SiteA</td><td>SiteB</td><td>Grand Total</td></tr><tr><td>3000</td><td> </td><td>5000</td><td> </td><td>5000</td></tr><tr><td>3005</td><td> </td><td> </td><td>3500</td><td>3500</td></tr><tr><td>3010</td><td>50</td><td> </td><td> </td><td>50</td></tr><tr><td>3300</td><td>25</td><td> </td><td> </td><td>25</td></tr><tr><td>4000</td><td> </td><td>-2000</td><td> </td><td>-2000</td></tr><tr><td>4005</td><td> </td><td> </td><td>-1000</td><td>-1000</td></tr><tr><td>4500</td><td> </td><td>-700</td><td> </td><td>-700</td></tr><tr><td>4505</td><td> </td><td> </td><td>-300</td><td>-300</td></tr><tr><td>4600</td><td> </td><td>-200</td><td> </td><td>-200</td></tr><tr><td>4605</td><td> </td><td> </td><td>-75</td><td>-75</td></tr><tr><td>4750</td><td> </td><td>-45</td><td> </td><td>-45</td></tr><tr><td>5000</td><td>-1000</td><td> </td><td> </td><td>-1000</td></tr><tr><td>Grand Total</td><td>-925</td><td>2055</td><td>2125</td><td>3255</td></tr></table>
 </body></html>

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

=-ROUND(Amount,0)
This message was edited by Mark W. on 2002-04-10 10:04
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top