Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot tables calculated fields
I am using Excel 2003 SP3.
I can only work with the Pivot Table or Pivot Chart, not with the actual source data (it is a "view," something I'm not really familiar with, but I'm pretty sure it is unavailable for me to even look at). The problem I have is that my yields are being messed up. If a serial number passes, or has only one defect code, then it is only counted once; but if there are multiple defect codes, then of course, that serial number gets counted for each time it occurs. I've seen these formulas in here for doing unique counts in a regular spreadsheet, or in the source data which can be added to a pivot table as a new field: = SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) = SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100)) = IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))1,0,1) Since I can't enter a range, is there anyway to modify these to be a calculated field? There are certain small, but apparently important, parts of the formulas above that I don't understand (e.g <""), so it's probably just a matter of knowing which field (DefectCode or SerialNumber) to insert in place of the ranges, but I can't find something that will work. It is such a shame to actually find a flaw in pivot tables! I can't believe they can do so many different kinds of summarizing except for the one kind I really need . . . Thanks -- - Kate |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot tables calculated fields
Hi,
you are right you can do lot of things but always what you need you don't. what helped me is to add that calculation in a new column and then make it as part of your Pivot table Hope this helps "chastaink" wrote: I am using Excel 2003 SP3. I can only work with the Pivot Table or Pivot Chart, not with the actual source data (it is a "view," something I'm not really familiar with, but I'm pretty sure it is unavailable for me to even look at). The problem I have is that my yields are being messed up. If a serial number passes, or has only one defect code, then it is only counted once; but if there are multiple defect codes, then of course, that serial number gets counted for each time it occurs. I've seen these formulas in here for doing unique counts in a regular spreadsheet, or in the source data which can be added to a pivot table as a new field: = SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) = SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100)) = IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))1,0,1) Since I can't enter a range, is there anyway to modify these to be a calculated field? There are certain small, but apparently important, parts of the formulas above that I don't understand (e.g <""), so it's probably just a matter of knowing which field (DefectCode or SerialNumber) to insert in place of the ranges, but I can't find something that will work. It is such a shame to actually find a flaw in pivot tables! I can't believe they can do so many different kinds of summarizing except for the one kind I really need . . . Thanks -- - Kate |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot tables calculated fields
Thanks, but I can't add anything, including calculations and columns, to my
source data. I really think the key could be found in adding a calculated field. Can anyone help with this problem please? Thanks -- - Kate "Eduardo" wrote: Hi, you are right you can do lot of things but always what you need you don't. what helped me is to add that calculation in a new column and then make it as part of your Pivot table Hope this helps "chastaink" wrote: I am using Excel 2003 SP3. I can only work with the Pivot Table or Pivot Chart, not with the actual source data (it is a "view," something I'm not really familiar with, but I'm pretty sure it is unavailable for me to even look at). The problem I have is that my yields are being messed up. If a serial number passes, or has only one defect code, then it is only counted once; but if there are multiple defect codes, then of course, that serial number gets counted for each time it occurs. I've seen these formulas in here for doing unique counts in a regular spreadsheet, or in the source data which can be added to a pivot table as a new field: = SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) = SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100)) = IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))1,0,1) Since I can't enter a range, is there anyway to modify these to be a calculated field? There are certain small, but apparently important, parts of the formulas above that I don't understand (e.g <""), so it's probably just a matter of knowing which field (DefectCode or SerialNumber) to insert in place of the ranges, but I can't find something that will work. It is such a shame to actually find a flaw in pivot tables! I can't believe they can do so many different kinds of summarizing except for the one kind I really need . . . Thanks -- - Kate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculated fields in pivot tables | Excel Discussion (Misc queries) | |||
Calculated Fields and Pivot Tables | Excel Worksheet Functions | |||
Calculated Fields in Pivot Tables | Excel Worksheet Functions | |||
Calculated fields-Pivot tables | Excel Worksheet Functions | |||
Pivot tables - calculated fields | Excel Worksheet Functions |