View Single Post
  #1   Report Post  
Old March 29th 06, 10:09 PM posted to microsoft.public.excel.misc
Posts: n/a
Default 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.


Thank you.