Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 25th 06, 09:01 PM posted to microsoft.public.excel.misc
 
Posts: n/a
Default Calculated field in Pivot Table

I have a dataset like the one below (not sure if formatting will come
out right in the posting). The example below is just a simplication of
my actual dataset.
Group Series Status
A A1 Open
A A2 Open
A A3 Unknown
A A9 Closed
A A10 Open
B B1 Closed
B B4 Closed
B B5 Closed
B B6 Closed
C C1 Unknown
D D1 Closed
D D2 Closed
E E1 Open
E E2 Open

What I want to do is count how many Groups fall into the status Open
bucket, how many into Closed, etc. Now because Group A has a Series in
each of the three possible Statuses, it falls into all three buckets.
Group E, on the other hand, only falls into one. If I pull Status into
a Pivot as a Column Field and then do a Count of Group, the values will
actually represent the number of Series in a given Status because the
value A appears in the dataset once for each corresponding Series in
that Group. Have I lost you yet? My hypothesis is that there is a
creative way to use calculated fields in a pivot to get it to show that
there are 2 groups showing Open, 2 showing Unknown, and 3 showing
Closed. I already know I could arrange the pivot in such a way that
=COUNTA( ) in the adjacent cells would give me the same thing, but I'm
looking for a way of doing it in the pivot for reason not worth wasting
any more space on. THANK IN ADVANCE FOR YOUR HELP!


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 benb Excel Discussion (Misc queries) 2 January 25th 06 10:48 PM
calculated field in a pivot table? baabaa Excel Discussion (Misc queries) 1 November 25th 05 09:08 AM
Using a MIN, MAX formula on a calculated field in a pivot table fhaberland Excel Discussion (Misc queries) 0 August 2nd 05 07:47 PM
Pivot table page field switch to (all) if my criteria is not avail Angus Excel Discussion (Misc queries) 0 July 30th 05 05:06 AM
Problems with Pivot Table Field Sorting in Excel 2002 Phoenix71555 Excel Discussion (Misc queries) 1 February 28th 05 12:25 AM


All times are GMT +1. The time now is 07:24 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