Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 29th 06, 10:09 PM posted to microsoft.public.excel.misc
cmlits
 
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.

PLEASE HELP.

Thank you.


  #2   Report Post  
Old March 30th 06, 05:44 AM posted to microsoft.public.excel.misc
Debra Dalgleish
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculated field in Pivot Table [email protected] Excel Discussion (Misc queries) 0 January 25th 06 08:01 PM
Sum of Calculated fields in Pivot Table Graham Excel Discussion (Misc queries) 0 July 4th 05 03:47 PM
Calculated field in pivot table Dan Excel Discussion (Misc queries) 1 April 19th 05 11:06 PM
How to create a calculated field formula based on Pivot Table resu dha17 Excel Discussion (Misc queries) 1 December 15th 04 05:39 AM
Pivot table help:calculated field based on previous consecutive va martin Excel Discussion (Misc queries) 0 December 9th 04 08:33 PM


All times are GMT +1. The time now is 04:25 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017