Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
calculated field in a pivot table? | Excel Discussion (Misc queries) | |||
Using a MIN, MAX formula on a calculated field in a pivot table | Excel Discussion (Misc queries) | |||
Pivot table page field switch to (all) if my criteria is not avail | Excel Discussion (Misc queries) | |||
Problems with Pivot Table Field Sorting in Excel 2002 | Excel Discussion (Misc queries) |