Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 25th 06, 08:56 PM posted to microsoft.public.excel.misc
benb
 
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!


  #2   Report Post  
Old January 25th 06, 10:14 PM posted to microsoft.public.excel.misc
Debra Dalgleish
 
Posts: n/a
Default Calculated field in Pivot Table

You could add a column to the source data, and calculate if a record is
the first group/status item. For example,

=IF(SUMPRODUCT(--($A$2:$A2=A2),--($C$2:$C2=C2))=1,1,0)

Copy the formula to the last row of data.
Add the new field to the data area as a sum

benb wrote:
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!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #3   Report Post  
Old January 25th 06, 10:48 PM posted to microsoft.public.excel.misc
benb
 
Posts: n/a
Default Calculated field in Pivot Table

Thanks Debra. That's a really good workaround that I would not have
caught and brings me close to an alternate solution for what I'm doing
without using a pivot at all.

Apart from practical concerns, my inquiry is one part stubborness, one
part learning for learning's sake. I am really unaccustomed to how
calculated fields work in pivots so I would like to learn more about
using them and I am convinced (perhaps wrongly so) that their
application can offer a solution here.

For now, I can do what I need to get done. If anyone has some insight
into how a calculated field might be used, though, I'm still very
curious to hear.

Thanks again.


Debra Dalgleish wrote:
You could add a column to the source data, and calculate if a record is
the first group/status item. For example,

=IF(SUMPRODUCT(--($A$2:$A2=A2),--($C$2:$C2=C2))=1,1,0)

Copy the formula to the last row of data.
Add the new field to the data area as a sum

benb wrote:
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!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




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
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
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 08:45 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