#VALUE! in PivotTable using Calculated Field
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","") |
#VALUE! in PivotTable using Calculated Field
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 wrote: 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 Contextures http://www.contextures.com/tiptech.html |
#VALUE! in PivotTable using Calculated Field
Debra, Thank you soooo much. This worked perfectly. You really saved me.
Johnnie "Debra Dalgleish" wrote: 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 wrote: 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 Contextures http://www.contextures.com/tiptech.html |
All times are GMT +1. The time now is 12:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com