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
|
|||
|
|||
How do I perform extended calculations in a Pivot Table in Excel 2
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. |
#2
|
|||
|
|||
How do I perform extended calculations in a Pivot Table in Excel2
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. |
#3
|
|||
|
|||
How do I perform extended calculations in a Pivot Table in Exc
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" wrote: 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. |
#4
|
|||
|
|||
How do I perform extended calculations in a Pivot Table in Exc
learnlearn52 wrote:
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! |
#5
|
|||
|
|||
How do I perform extended calculations in a Pivot Table in Exc
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" wrote: learnlearn52 wrote: 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! |
Thread Tools | |
Display Modes | |
|
|