Pivot table

[2077delta]

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?

[lars]

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)

[Mark W.]

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 ]

[EdE]

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??

[Mark W.]

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 ]

[2077delta]

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.

[Mark W.]

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..."?

[2077delta]

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.

[Mark W.]

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 ]

[2077delta]

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.