Hi
The only way I can think of doing this if you do not want your expenses to
be negative values, would be to right click on the
Pivot Table>Table Options>uncheck Grand Total by columns
Then, in the blank row above your Sum of Amount (or whatever you have titled
it) Enter the Title "Profit" and in the cell above the Total column enter a
GetPivotdata formula some thing like
=GETPIVOTDATA("amount",$J$4,"type","i")-GETPIVOTDATA("amount",$J$4,"type","e")
In my case Type is a filed which holds "i" for Income and "e" for
Expenditure
J4 is the cell holding Sum of Amount
Adjust to suit your case.
Using GetPivotdata as opposed to direct cell reference, will adjust as the
PT grows or shrinks
It is not pretty, and may not be acceptable to you, but without having
negative expenses I cannot see any other way to make it work.
The only "proper" way to do it, leaving your source data as all positive
values, would be to add another column "Amount2" with a formula like
=IF(A2="e",C2*-1,C2)
where column A holds type and column C holds value.
Allocate Amount2 to the PT in place of Amount, and switch Grand Totals by
column back on.
--
Regards
Roger Govier
snax500 said:
I have a pivot table with revenues and expenses. The revenues has to
shown as a positive and the expenses are positive.I then want the
grand total to be the difference betweem the revenue and expenses. I
dont want to change the data just the pivot. Thanks.
__________ Information from ESET Smart Security, version of virus
signature database 4508 (20091014) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus signature database 4508 (20091014) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus signature database 4509 (20091015) __________
The message was checked by ESET Smart Security.
http://www.eset.com