#VALUE! in PivotTable using Calculated Field

[Johnnie]

I'm working with a calculated field in a PivotTable. The formula I am using
in the calculated field is below. The value in one row for UL is 0.2471 and
the value for LL is 0. I am getting #VALUE! for the result in the
PivotTable. I am actually getting the Reject value if I perform the same
function outside of the PivotTable. Can anyone help me understand why I'm
getting this error in the PivtoTable, but not getting it outside the table?

=IF(OR(UL<0.5,0.5<LL),"Reject","")

 

[Debra Dalgleish]

The data area can't show text results from a formula, so that's what is
causing the error.

Change your formula so it returns numeric values, e.g.:
=IF(OR(UL<0.5,0.5<LL),99,0)
Then, right-click on the heading for the calculated field, and click on
Field Settings
Click Number
For Category, click on Custom
In the Type box, enter: [=99]"Reject";[=0]"";General
Click OK, twice, to close the dialog boxes.

 

[Johnnie]

Debra, Thank you soooo much. This worked perfectly. You really saved me.

Johnnie

The data area can't show text results from a formula, so that's what is
causing the error.

Change your formula so it returns numeric values, e.g.:
=IF(OR(UL<0.5,0.5<LL),99,0)
Then, right-click on the heading for the calculated field, and click on
Field Settings
Click Number
For Category, click on Custom
In the Type box, enter: [=99]"Reject";[=0]"";General
Click OK, twice, to close the dialog boxes.

I'm working with a calculated field in a PivotTable. The formula I am using
in the calculated field is below. The value in one row for UL is 0.2471 and
the value for LL is 0. I am getting #VALUE! for the result in the
PivotTable. I am actually getting the Reject value if I perform the same
function outside of the PivotTable. Can anyone help me understand why I'm
getting this error in the PivtoTable, but not getting it outside the table?

=IF(OR(UL<0.5,0.5<LL),"Reject","")