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 |
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 |