Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totals of calculated field in pivot table give incorrect results
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.worksheet.functions
|
|||
|
|||
Totals of calculated field in pivot table give incorrect results
Hi Jake
I agree with you. The PT does appear to be calculating the total AMT2 incorrectly by doing the .31*total Amount, and not summing the individual amounts where the conditional test would have made calculation of some of the individual AMT2's zero. The only way I could get around it, was to not use a calculated field in the PT, but to have an extra calculated column in the source data. My test data had columns starting with column A of Name, Date, Amount, Amount2 in row 1 Rows 2:9 carried values in columns A:C In D2 I entered the following formula =IF(SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)0, SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)*0.31,0) /SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2)) and copied down through D3:D9 I then added Amount2 to the Data area, having first deleted the calculated field AMT2 -- 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totals of calculated field in pivot table give incorrect resul
Thanks Roger, looks as if that will work. But I feel as if I'm back in Lotus
1A! I've never used sumproduct function before. I'll experiment. Regards, Jake "Roger Govier" wrote: Hi Jake I agree with you. The PT does appear to be calculating the total AMT2 incorrectly by doing the .31*total Amount, and not summing the individual amounts where the conditional test would have made calculation of some of the individual AMT2's zero. The only way I could get around it, was to not use a calculated field in the PT, but to have an extra calculated column in the source data. My test data had columns starting with column A of Name, Date, Amount, Amount2 in row 1 Rows 2:9 carried values in columns A:C In D2 I entered the following formula =IF(SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)0, SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)*0.31,0) /SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2)) and copied down through D3:D9 I then added Amount2 to the Data area, having first deleted the calculated field AMT2 -- 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totals of calculated field in pivot table give incorrect resul
Hi Jake
Yes, its a real PITA that the PT won't do it correctly, but Sumproduct isn't that horrendous once you get into it. Named ranges of course make the whole thing more readable. Bob Phillips has a good treatise on the subject you might want to read http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "Jake" wrote in message ... Thanks Roger, looks as if that will work. But I feel as if I'm back in Lotus 1A! I've never used sumproduct function before. I'll experiment. Regards, Jake "Roger Govier" wrote: Hi Jake I agree with you. The PT does appear to be calculating the total AMT2 incorrectly by doing the .31*total Amount, and not summing the individual amounts where the conditional test would have made calculation of some of the individual AMT2's zero. The only way I could get around it, was to not use a calculated field in the PT, but to have an extra calculated column in the source data. My test data had columns starting with column A of Name, Date, Amount, Amount2 in row 1 Rows 2:9 carried values in columns A:C In D2 I entered the following formula =IF(SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)0, SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)*0.31,0) /SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2)) and copied down through D3:D9 I then added Amount2 to the Data area, having first deleted the calculated field AMT2 -- 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totals of calculated field in pivot table give incorrect resul
Thanks. I've never spent the time to look at array functions. Hard to teach
an old dog new tricks. The article looks helpful. Regards, Jake "Roger Govier" wrote: Hi Jake Yes, its a real PITA that the PT won't do it correctly, but Sumproduct isn't that horrendous once you get into it. Named ranges of course make the whole thing more readable. Bob Phillips has a good treatise on the subject you might want to read http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "Jake" wrote in message ... Thanks Roger, looks as if that will work. But I feel as if I'm back in Lotus 1A! I've never used sumproduct function before. I'll experiment. Regards, Jake "Roger Govier" wrote: Hi Jake I agree with you. The PT does appear to be calculating the total AMT2 incorrectly by doing the .31*total Amount, and not summing the individual amounts where the conditional test would have made calculation of some of the individual AMT2's zero. The only way I could get around it, was to not use a calculated field in the PT, but to have an extra calculated column in the source data. My test data had columns starting with column A of Name, Date, Amount, Amount2 in row 1 Rows 2:9 carried values in columns A:C In D2 I entered the following formula =IF(SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)0, SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)*0.31,0) /SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2)) and copied down through D3:D9 I then added Amount2 to the Data area, having first deleted the calculated field AMT2 -- 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totals of calculated field in pivot table give incorrect resul
Got it to work. Thanks. Puzzled over the last operation (dividing by sum of
trues) until I realized only way to ensure multiplicand was either 1 or 0 when using mulitple criterea. Thanks. This will be very useful! I'm going to create my own "two-way" table: unique extract of customer as row lables, and relevant dates as column headings. use sumproduct in each cell with the row and column lables as my criterea. Think that will work? "Roger Govier" wrote: Hi Jake Yes, its a real PITA that the PT won't do it correctly, but Sumproduct isn't that horrendous once you get into it. Named ranges of course make the whole thing more readable. Bob Phillips has a good treatise on the subject you might want to read http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "Jake" wrote in message ... Thanks Roger, looks as if that will work. But I feel as if I'm back in Lotus 1A! I've never used sumproduct function before. I'll experiment. Regards, Jake "Roger Govier" wrote: Hi Jake I agree with you. The PT does appear to be calculating the total AMT2 incorrectly by doing the .31*total Amount, and not summing the individual amounts where the conditional test would have made calculation of some of the individual AMT2's zero. The only way I could get around it, was to not use a calculated field in the PT, but to have an extra calculated column in the source data. My test data had columns starting with column A of Name, Date, Amount, Amount2 in row 1 Rows 2:9 carried values in columns A:C In D2 I entered the following formula =IF(SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)0, SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)*0.31,0) /SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2)) and copied down through D3:D9 I then added Amount2 to the Data area, having first deleted the calculated field AMT2 -- 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totals of calculated field in pivot table give incorrect resul
Hi Jake
Glad you got it sorted. The last part of the formula, was to deal with scenarios (if they exist), where the same customer crops up with transactions more than once in each day. Sumproduct will find all of the values belonging to that customer for that day and produce the correct total for the day, but if the customer name crops up again further down the list, then the same total value for that same day would be displayed again. With that data then being passed to a Pivot table, the Pivot table would sum the values where customer and day agree, so it would give a value of 2 or 3 times the correct value. By dividing by the sum of true's, then the value attributed to each customer, when summed through the PT comes back to the correct value. Now if you are going to create your own 2 dimensional matrix, with sumproduct to calculate the total based upon the row and column header, you won't need that last part of the formula at all, since you will only be summing by a single occurrence of the customer in the data array, for any given date not by each occurrence of his name for any given date. Personally, I prefer the PT approach. It is so much easier to change the view dragging different fields in and out of different areas of the table, especially with the use of Page fields to select a given year or given month. I seldom tend to use calculated fields inside the PT itself, and I will certainly avoid them when making comparative type operations as you had done now I realise that there is a bug there. Let's hope they get it fixed for Excel 12, which looks to have lots of juicy new features when it comes to PT's. By pure chance, I have just answered a post in another thread with a similar problem -- Regards Roger Govier "Jake" wrote in message ... Got it to work. Thanks. Puzzled over the last operation (dividing by sum of trues) until I realized only way to ensure multiplicand was either 1 or 0 when using mulitple criterea. Thanks. This will be very useful! I'm going to create my own "two-way" table: unique extract of customer as row lables, and relevant dates as column headings. use sumproduct in each cell with the row and column lables as my criterea. Think that will work? "Roger Govier" wrote: Hi Jake Yes, its a real PITA that the PT won't do it correctly, but Sumproduct isn't that horrendous once you get into it. Named ranges of course make the whole thing more readable. Bob Phillips has a good treatise on the subject you might want to read http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "Jake" wrote in message ... Thanks Roger, looks as if that will work. But I feel as if I'm back in Lotus 1A! I've never used sumproduct function before. I'll experiment. Regards, Jake "Roger Govier" wrote: Hi Jake I agree with you. The PT does appear to be calculating the total AMT2 incorrectly by doing the .31*total Amount, and not summing the individual amounts where the conditional test would have made calculation of some of the individual AMT2's zero. The only way I could get around it, was to not use a calculated field in the PT, but to have an extra calculated column in the source data. My test data had columns starting with column A of Name, Date, Amount, Amount2 in row 1 Rows 2:9 carried values in columns A:C In D2 I entered the following formula =IF(SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)0, SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2),$B$2:$B$9)*0.31,0) /SUMPRODUCT(--($A$2:$A$9=A2),--($C$2:$C$9=C2)) and copied down through D3:D9 I then added Amount2 to the Data area, having first deleted the calculated field AMT2 -- 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 | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
calculated field in a pivot table? | Excel Discussion (Misc queries) | |||
Pivot Table Page Field Area Broken | Excel Discussion (Misc queries) | |||
Pivot Table Data Field Query | Excel Worksheet Functions | |||
Calculated field in pivot table | Excel Discussion (Misc queries) |