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.

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.