Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot formula
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot formula
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 TableTable Optionsuncheck 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot formula | Excel Discussion (Misc queries) | |||
Pivot Formula | Excel Discussion (Misc queries) | |||
cancelling getpivot formula when setting formula outside pivot ta. | Excel Worksheet Functions | |||
deactivate the get pivot formula when I link a cell to a pivot | New Users to Excel | |||
Add a formula to Pivot Table | Excel Worksheet Functions |