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 ?

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 ?

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 ?