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

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

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