Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating differences in a pivot table
I use a pivot table to show sales by customer. i am looking at sales
for year 2006 and 2007. i placed the customer name field in the row label, and the sales amount field in the values area of the pivot table. i placed the year field in the column label to look at the 2 years seperately. is there a way to use the pivot table to calculate the difference between the 2 years for each customer rather than calculating the grand total for the 2 years for each customer. thanks. T |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating differences in a pivot table
Hi
In your PT, right click and choose Table Options. De-select Grand Totals by Row. Right click again and choose Pivot Table WizardLayoutdrag Sales amount to the data area a second time. Double click on the Sum of Sales2 icon and from the dropdown Show Data aschoose Difference FromBase Field YearBase Item 2006OK.OK.Finish. On the PT, drag the Data button and drop on Total and you will see the values side by side. -- Regards Roger Govier wrote in message ups.com... I use a pivot table to show sales by customer. i am looking at sales for year 2006 and 2007. i placed the customer name field in the row label, and the sales amount field in the values area of the pivot table. i placed the year field in the column label to look at the 2 years seperately. is there a way to use the pivot table to calculate the difference between the 2 years for each customer rather than calculating the grand total for the 2 years for each customer. thanks. T |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating differences in a pivot table
On Sep 26, 6:17 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote: Hi In your PT, right click and choose Table Options. De-select Grand Totals by Row. Right click again and choose Pivot Table WizardLayoutdrag Sales amount to the data area a second time. Double click on the Sum of Sales2 icon and from the dropdown Show Data aschoose Difference FromBase Field YearBase Item 2006OK.OK.Finish. On the PT, drag the Data button and drop on Total and you will see the values side by side. -- Regards Roger Govier wrote in message ups.com... I use a pivot table to show sales by customer. i am looking at sales for year 2006 and 2007. i placed the customer name field in the row label, and the sales amount field in the values area of the pivot table. i placed the year field in the column label to look at the 2 years seperately. is there a way to use the pivot table to calculate the difference between the 2 years for each customer rather than calculating the grand total for the 2 years for each customer. thanks. T- Hide quoted text - - Show quoted text - Thanks. I removed the grand totals for each row and i added a 2nd field for sum of sales amt. then i changed the data for the 2nd sales amt field to be displayed as a difference from base yr 2006 like you said above. I get #N/A as the resulting value. Also, i am not sure it makes a difference, but i am using Excel '07. Thanks. T |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating differences in a pivot table
Hi
No, it works in XL2007 as well. If you want, send me a copy of your file and I will set it up for you. To send direct, email to roger at technology4u dot co dot uk Do the obvious with at and dot in the email address. -- Regards Roger Govier wrote in message ups.com... On Sep 26, 6:17 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi In your PT, right click and choose Table Options. De-select Grand Totals by Row. Right click again and choose Pivot Table WizardLayoutdrag Sales amount to the data area a second time. Double click on the Sum of Sales2 icon and from the dropdown Show Data aschoose Difference FromBase Field YearBase Item 2006OK.OK.Finish. On the PT, drag the Data button and drop on Total and you will see the values side by side. -- Regards Roger Govier wrote in message ups.com... I use a pivot table to show sales by customer. i am looking at sales for year 2006 and 2007. i placed the customer name field in the row label, and the sales amount field in the values area of the pivot table. i placed the year field in the column label to look at the 2 years seperately. is there a way to use the pivot table to calculate the difference between the 2 years for each customer rather than calculating the grand total for the 2 years for each customer. thanks. T- Hide quoted text - - Show quoted text - Thanks. I removed the grand totals for each row and i added a 2nd field for sum of sales amt. then i changed the data for the 2nd sales amt field to be displayed as a difference from base yr 2006 like you said above. I get #N/A as the resulting value. Also, i am not sure it makes a difference, but i am using Excel '07. Thanks. T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating age differences | Excel Discussion (Misc queries) | |||
Calculating Differences between columns in a Pivot Table | Excel Discussion (Misc queries) | |||
Calculating Differences Uniformly | Excel Worksheet Functions | |||
Calculating differences in dates | Excel Discussion (Misc queries) | |||
Calculating differences between dates | Excel Worksheet Functions |