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
|
|||
|
|||
Comparing % Differences From in Pivot Tables
In Excel 2007 (which I LOVE!) I have the following pivot table, and I need to
calculate average, min, max, and stdev in % Diff From columns (see question marks in the example below). % Diff From is calculated between columns Visit 2 and Visit 1. The values I get wrong, how do I do this correctly? Product Subject Visit 1 Visit 2 Product 1 1 -23.24% 2 -15.26% 3 12.32% 4 0.96% 5 7.34% Product 1 Average ? Product 1 Max ? Product 1 Min ? Product 1 StdDev ? Product 2 1 -5.20% 2 21.12% 3 60.76% 4 -52.86% 5 76.00% Product 2 Average ? Product 2 Max ? Product 2 Min ? Product 2 StdDev ? Grand Total Avg ? Grand Total Max ? Grand Total Min ? Grand Total Stdev ? |
#2
|
|||
|
|||
Comparing % Differences From in Pivot Tables
If I understand your question, I've had the same difficulty and the problem
lies in that it's difficult to calculate formulas from the pivot table. I get around this by inserting another tab and doing the following: copy the pivot table / paste on new tab / copy the pivot table you just pasted on the new tab and paste as a value. If you paste first then paste as a value it will keep the formatting of the pivot table. If you just copy and paste value it just gives you the values, unformatted. It just helps to make it a little neater. Now that you have just the values, you can execute any calculations you like. Good luck! "Julia" wrote: In Excel 2007 (which I LOVE!) I have the following pivot table, and I need to calculate average, min, max, and stdev in % Diff From columns (see question marks in the example below). % Diff From is calculated between columns Visit 2 and Visit 1. The values I get wrong, how do I do this correctly? Product Subject Visit 1 Visit 2 Product 1 1 -23.24% 2 -15.26% 3 12.32% 4 0.96% 5 7.34% Product 1 Average ? Product 1 Max ? Product 1 Min ? Product 1 StdDev ? Product 2 1 -5.20% 2 21.12% 3 60.76% 4 -52.86% 5 76.00% Product 2 Average ? Product 2 Max ? Product 2 Min ? Product 2 StdDev ? Grand Total Avg ? Grand Total Max ? Grand Total Min ? Grand Total Stdev ? |
#3
|
|||
|
|||
Comparing % Differences From in Pivot Tables
Thank you! That's what I ended up doing, but I have so much data, that it
will take me at least 1/2 day to complete all the calculations. Would have been nice to use the pivot tables. "smartgal" wrote: If I understand your question, I've had the same difficulty and the problem lies in that it's difficult to calculate formulas from the pivot table. I get around this by inserting another tab and doing the following: copy the pivot table / paste on new tab / copy the pivot table you just pasted on the new tab and paste as a value. If you paste first then paste as a value it will keep the formatting of the pivot table. If you just copy and paste value it just gives you the values, unformatted. It just helps to make it a little neater. Now that you have just the values, you can execute any calculations you like. Good luck! "Julia" wrote: In Excel 2007 (which I LOVE!) I have the following pivot table, and I need to calculate average, min, max, and stdev in % Diff From columns (see question marks in the example below). % Diff From is calculated between columns Visit 2 and Visit 1. The values I get wrong, how do I do this correctly? Product Subject Visit 1 Visit 2 Product 1 1 -23.24% 2 -15.26% 3 12.32% 4 0.96% 5 7.34% Product 1 Average ? Product 1 Max ? Product 1 Min ? Product 1 StdDev ? Product 2 1 -5.20% 2 21.12% 3 60.76% 4 -52.86% 5 76.00% Product 2 Average ? Product 2 Max ? Product 2 Min ? Product 2 StdDev ? Grand Total Avg ? Grand Total Max ? Grand Total Min ? Grand Total Stdev ? |
Thread Tools | |
Display Modes | |
|
|