Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Incorrect totals in Pivot Table
I have created a calculated field in my pivot table. It works properly.
However, the column and row totals do not give intended results (sum of displayed results). Rather they use the same formula on all data in that part of the table. Here are the details: Data Table shows payments by transaction for all customers (field name=AMT). Customer may have + and - payments on any day. I need to show net payments per day by customer. I then need to calculate 31% of net payment BUT only if net 0. Pivot table: created 2-way table, calculating sum of pmts by customer by day. works fine calculated field: =if(AMT0,AMT*.31,0). works fine in body of table totals: table does not calculate total of displayed results of formula, rather reruns formula on underlying data. example: 2 customers, one has net pmts of 30, formula shows 9.3, second has net of -20, formula shows 0. I want total of 9.3. table shows total of 3.1 (30-20)*.31. Any suggestions? Thanks, Jake |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Incorrect totals in Pivot Table
see response in worksheet.functions
-- Regards Roger Govier "Jake" wrote in message ... I have created a calculated field in my pivot table. It works properly. However, the column and row totals do not give intended results (sum of displayed results). Rather they use the same formula on all data in that part of the table. Here are the details: Data Table shows payments by transaction for all customers (field name=AMT). Customer may have + and - payments on any day. I need to show net payments per day by customer. I then need to calculate 31% of net payment BUT only if net 0. Pivot table: created 2-way table, calculating sum of pmts by customer by day. works fine calculated field: =if(AMT0,AMT*.31,0). works fine in body of table totals: table does not calculate total of displayed results of formula, rather reruns formula on underlying data. example: 2 customers, one has net pmts of 30, formula shows 9.3, second has net of -20, formula shows 0. I want total of 9.3. table shows total of 3.1 (30-20)*.31. Any suggestions? Thanks, Jake |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Totals of calculated field in pivot table give incorrect results | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Totals For a Pivot Table?? | Excel Discussion (Misc queries) | |||
Pivot Table Grand Totals | Excel Worksheet Functions | |||
How do I show summary totals from a pivot table on a bar chart | Charts and Charting in Excel |