How do I perform extended calculations in a Pivot Table in Excel 2

[learnlearn52]

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 are:

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.

 

[smartin]

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 are:

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.

 

[learnlearn52]

smartin:

Thanks for your response. I have considered the PT option for the following
reasons. I am tracking data for two types of products as they are being built
on a weekly basis and they are entered into the raw data. With the PT, I can
review the data with the pull-down option for each item in the Page Fields
area of the PT. By doing so I can prepare only one dynamic chart and pick
which Item for which I want the information to be displayed (both the monthly
data as well as the chart).

If the data can be manipulated (calculated) in the raw data table then the
info can be displayed in the PT also and the dynamic charts can be created
with ease.

I have displayed (limited amount as an example) a typical table which shows
the tracking of the data for the two Items.


Feb-08 Item 1 Fail
Feb-08 Item 1 Pass
Feb-08 Item 2 Pass
Feb-08 Item 1 Fail
Feb-08 Item 2 Pass
Feb-08 Item 1 Pass
Feb-08 Item 1 Pass
Feb-08 Item 2 Fail
Feb-08 Item 2 Pass
Feb-08 Item 1 Pass
Feb-08 Item 1 Fail
Mar-08 Item 2 Pass
Mar-08 Item 1 Fail
Mar-08 Item 1 Pass
Mar-08 Item 2 Fail
Mar-08 Item 1 Pass
Mar-08 Item 1 Pass
Mar-08 Item 2 Pass
Mar-08 Item 2 Pass
Mar-08 Item 1 Pass
Mar-08 Item 1 Pass
Apr-08 Item 1 Pass
Apr-08 Item 2 Pass
Apr-08 Item 1 Fail
Apr-08 Item 2 Fail
Apr-08 Item 2 Pass
Apr-08 Item 1 Fail
Apr-08 Item 2 Pass
Apr-08 Item 1 Pass
Apr-08 Item 2 Pass
Apr-08 Item 2 Fail
May-08 Item 1 Fail
May-08 Item 2 Pass
May-08 Item 1 Pass
May-08 Item 2 Fail
May-08 Item 2 Fail
May-08 Item 1 Pass
Jun-08 Item 2 Pass
Jun-08 Item 1 Fail
Jun-08 Item 2 Fail
Jun-08 Item 2 Fail

Based on these data, can I create two more columns showing the cumulative
totals as well as the cumulative grand totals for each Item. If yes, what
formulas may be used. One can then use these numbers to calculate the % and
they can be made part of the PT. The dynamic charts based on the PT are then
a breeze. I hope this is making sense.

Thanks once again.

 

[smartin]

smartin:

Thanks for your response. I have considered the PT option for the following
reasons. I am tracking data for two types of products as they are being built
on a weekly basis and they are entered into the raw data. With the PT, I can
review the data with the pull-down option for each item in the Page Fields
area of the PT. By doing so I can prepare only one dynamic chart and pick
which Item for which I want the information to be displayed (both the monthly
data as well as the chart).

If the data can be manipulated (calculated) in the raw data table then the
info can be displayed in the PT also and the dynamic charts can be created
with ease.

I have displayed (limited amount as an example) a typical table which shows
the tracking of the data for the two Items.


Feb-08 Item 1 Fail [snipped]
Jun-08 Item 2 Fail

Based on these data, can I create two more columns showing the cumulative
totals as well as the cumulative grand totals for each Item. If yes, what
formulas may be used. One can then use these numbers to calculate the % and
they can be made part of the PT. The dynamic charts based on the PT are then
a breeze. I hope this is making sense.

Thanks once again.

To create cumulative as well as incremental totals with a PT, add the
value field (for you I think that's count of something) to the data area
a second time. Excel will add it as interlaced rows among the row
categories -- we'll fix that in a minute. Now change the field settings
for this new item: right click it, Options, Show Data As and select
"Running Total in". The base field you want is probably whatever the row
category is.

Now to rearrange this so the data are all in one row instead of
interlaced rows, drag the data handle and drop it as if you wanted to
move it to the column area. (Thanks to Mike Alexander for that last trick.)

Hope this helps!

 

[learnlearn52]

smartin:

Thank you for the follow-up. I tried the method as suggested by you. It only
added another for each month; the added rows are identical to the rows that
already are in the Count Field. Any other suggestions? I am using Excel 2003.
Does this make a difference?

Also, in my original raw data table I have created additional columns to
compute the cumulative and % using the following formulae. I was successful
for only one Item. Any suggestions to include the second Item in the formulae?

=COUNTIF($C$2,"Item1")
=SUMPRODUCT(($C$2="Item1")*($D$22="Pass"))

Hope this is making sense. Thanks in advance.

smartin:

Thanks for your response. I have considered the PT option for the following
reasons. I am tracking data for two types of products as they are being built
on a weekly basis and they are entered into the raw data. With the PT, I can
review the data with the pull-down option for each item in the Page Fields
area of the PT. By doing so I can prepare only one dynamic chart and pick
which Item for which I want the information to be displayed (both the monthly
data as well as the chart).

If the data can be manipulated (calculated) in the raw data table then the
info can be displayed in the PT also and the dynamic charts can be created
with ease.

I have displayed (limited amount as an example) a typical table which shows
the tracking of the data for the two Items.


Feb-08 Item 1 Fail [snipped]
Jun-08 Item 2 Fail

Based on these data, can I create two more columns showing the cumulative
totals as well as the cumulative grand totals for each Item. If yes, what
formulas may be used. One can then use these numbers to calculate the % and
they can be made part of the PT. The dynamic charts based on the PT are then
a breeze. I hope this is making sense.

Thanks once again.

To create cumulative as well as incremental totals with a PT, add the
value field (for you I think that's count of something) to the data area
a second time. Excel will add it as interlaced rows among the row
categories -- we'll fix that in a minute. Now change the field settings
for this new item: right click it, Options, Show Data As and select
"Running Total in". The base field you want is probably whatever the row
category is.

Now to rearrange this so the data are all in one row instead of
interlaced rows, drag the data handle and drop it as if you wanted to
move it to the column area. (Thanks to Mike Alexander for that last trick.)

Hope this helps!