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
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.
Excel FAQ, Tips & Book List
"cmlits" wrote in message
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.