#VALUE! in PivotTable using Calculated Field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Johnnie - 28 Mar 2008 20:26 GMT

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","")

Reply to this Message

Debra Dalgleish - 29 Mar 2008 00:20 GMT

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
[quoted text clipped - 4 lines]
>
> =IF(OR(UL<0.5,0.5<LL),"Reject","")

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Johnnie - 31 Mar 2008 14:23 GMT

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.
[quoted text clipped - 16 lines]
> >
> > =IF(OR(UL<0.5,0.5<LL),"Reject","")

Reply to this Message