If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Custom Formula in Pivot Table Row
Hello all,
I use Pivot Tables (Excel 2003) to create Profit & Loss statements. I would like to insert a custom formula to calculate 1) Gross Profit and 2) Gross Profit %. My pivot tables look something like this: Acc Type Account Budget Actual Variance Sales 5001 | 10000 12000 2000 5002 | 5000 4000 -1000 5003 | 2000 2500 500 ------------------------------------------------- Sales Total 17000 18500 1500 ------------------------------------------------- COS 6001 | 8000 8500 500 6002 | 2000 1500 -500 6003 | 500 1200 700 ------------------------------------------------- COS Total 10500 11200 700 ------------------------------------------------- Grand Total 27500 29700 2200 What I want to do is to rename the "Grand Total" row to "Gross Profit". Then, I want this row to subtract COS Total from Sales Total (for each column). I don't want to use a formula row outside of the pivot-table. Is this possible? Cam |
#2
|
|||
|
|||
Custom Formula in Pivot Table Row
Without seeing your database and pivot table it would be difficult to help
you. However you can research "calculated fields" and "calculated items" in the Excel help files. hope this helps. Gilles "Cam" wrote in message ps.com... Hello all, I use Pivot Tables (Excel 2003) to create Profit & Loss statements. I would like to insert a custom formula to calculate 1) Gross Profit and 2) Gross Profit %. My pivot tables look something like this: Acc Type Account Budget Actual Variance Sales 5001 | 10000 12000 2000 5002 | 5000 4000 -1000 5003 | 2000 2500 500 ------------------------------------------------- Sales Total 17000 18500 1500 ------------------------------------------------- COS 6001 | 8000 8500 500 6002 | 2000 1500 -500 6003 | 500 1200 700 ------------------------------------------------- COS Total 10500 11200 700 ------------------------------------------------- Grand Total 27500 29700 2200 What I want to do is to rename the "Grand Total" row to "Gross Profit". Then, I want this row to subtract COS Total from Sales Total (for each column). I don't want to use a formula row outside of the pivot-table. Is this possible? Cam |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pivot Table Formula | carolini | General Discussion | 1 | December 2nd, 2005 10:26 PM |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
pivot table sort entries that don't yet appear in table | rachael | General Discussion | 11 | September 19th, 2005 11:29 PM |
Formula checking multiple worksheets | sonic-the-mouse | Worksheet Functions | 11 | June 6th, 2005 06:37 PM |
Custom Views in Pivot Table | Samad | General Discussion | 1 | January 13th, 2005 01:14 AM |