learnlearn52 wrote:
How do I perform extended calculations in a Pivot Table in Excel 2003?

From the raw data in a worksheet, I created a Pivot Table shown below. How
do I:

1. add columns to the Pivot Table which would show me the additional
calculations?

2. Make these additional calculations dynamic so that whenever the original
worksheet is updated, the Pivot Tables and the calculations are updated or
refreshed automatically.


Fail Pass Grand Total
Jan-2008 4 6 10
Feb-2008 10 20 30
Mar-2008 3 17 20
Apr-2008 2 28 30
May-2008 1 39 40

Grand Total 20 110 130

The calculations I would like to perform a

1. A column adjacent to the Pass column showing the cumulative Pass
2. A second column to the right of the Grand Total showing the cumulative
Grand Total
3. A third column showing the calculated ratio of the Pass to Grand Total
column
4. A fourth column showing the calculated ratio of the Cumulative Pass
column to the Cumulative Grand Total column.

Currently, I am copying the Pivot Table and creating the percentages using
the Field Settings Option in this table. Additionally, I am manually creating
another table showing the cumulative totals and the corresponding percentages.

Please suggest a good book or reference on Pivot Tables that would cover
this type of topics and more!!! Thanks in advance.


I don't think a PT is the best tool for this job. You could add the
running totals well enough, even format them to appear in new columns
(as opposed to Excel's default of creating interlaced rows for multiple
data elements), but you are not going to be able to calculate the ratios
in the PT. You could place additional formulas outside the PT, but I do
not recommend this design approach.

Have you considered using a formula-driven approach instead of PT? Based
on your sample you could obtain the pass/fail results with a
two-category lookup (SUMPRODUCT works well for this), then writing the
cumulative values and ratios is a snap, and you can place the columns
wherever you wish. Also, formulas will update automatically, whereas
with a PT you must refresh the cache to pick up new data--which only
takes one click--but it is not automatic.