Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculated Field in Pivot Table Based on Two Counted Fields
Hope someone can help with this as I cannot figure this out and have
been staring at this for too long now... I have a need to create a Calculated Field based on Two Counted Fields in data area of my pivot table. 1) Date Starts out as the following, for example: State Customer ID Start Date End Date MA 01234 3/15/2006 - MA 55525 3/01/2006 3/02/2006 MA 48745 3/06/2006 3/10/2006 NH 52854 3/15/2006 3/16/2006 NH 47474 3/18/2006 NH 55481 3/20/2006 NH 88825 3/15/2006 2) I create a pivot table broken out by state counting on Start Date and End Date for the data Fields. So the Pivot looks like this: State Count of Start Date Count of End Date MA 3 2 NH 4 1 3) Now I want to find out the row percentage of those calculated fields. Meaning, I want to add a calculated filed (and therefore a third column) which would then show the percentage of (Count of End Date) / (Count of Start Date). Hoping it would end up like this: State Count of Start Date Count of End Date %Ended MA 3 2 66.67% NH 4 1 25% I've tried adding a calculated field but because I'm looking to create this field on two other calcluated fields, I cannot figure out how to get that Percentage Ended field created. PLEASE HELP. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculated Field in Pivot Table Based on Two Counted Fields
You could add a field to the source data, to calculate the completed
percentage. For example, in a column with the heading "%End", enter the formula: =COUNT(D2) where D2 is the first end date. Copy the formula down to the last row of data Refresh the pivot table, and add the %End column to the data area, as Average of %End. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html "cmlits" wrote in message oups.com... Hope someone can help with this as I cannot figure this out and have been staring at this for too long now... I have a need to create a Calculated Field based on Two Counted Fields in data area of my pivot table. 1) Date Starts out as the following, for example: State Customer ID Start Date End Date MA 01234 3/15/2006 - MA 55525 3/01/2006 3/02/2006 MA 48745 3/06/2006 3/10/2006 NH 52854 3/15/2006 3/16/2006 NH 47474 3/18/2006 NH 55481 3/20/2006 NH 88825 3/15/2006 2) I create a pivot table broken out by state counting on Start Date and End Date for the data Fields. So the Pivot looks like this: State Count of Start Date Count of End Date MA 3 2 NH 4 1 3) Now I want to find out the row percentage of those calculated fields. Meaning, I want to add a calculated filed (and therefore a third column) which would then show the percentage of (Count of End Date) / (Count of Start Date). Hoping it would end up like this: State Count of Start Date Count of End Date %Ended MA 3 2 66.67% NH 4 1 25% I've tried adding a calculated field but because I'm looking to create this field on two other calcluated fields, I cannot figure out how to get that Percentage Ended field created. PLEASE HELP. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculated field in Pivot Table | Excel Discussion (Misc queries) | |||
Sum of Calculated fields in Pivot Table | Excel Discussion (Misc queries) | |||
Calculated field in pivot table | Excel Discussion (Misc queries) | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) | |||
Pivot table help:calculated field based on previous consecutive va | Excel Discussion (Misc queries) |