How do I suppress a zero value or #VALUE! for a Pivot calculated i

  • Thread starter Thread starter DJL
  • Start date Start date
D

DJL

I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with #VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks
 
Hai,
Try this I didn't try it. Just given like that
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),(SUM(Budget)-SUM(Actual)=0)),"",SUM(Budget)-SUM(Actual))
 
Use 0 instead of "" in your forlula.

Use IFERROR instead of ISERROR.

Use IFERROR at the beginning of formula not inbetween the formula.

Try below formula;

=IFERROR(SUM(Budget)-SUM(Actual),0)

Regards,
Pritesh
 
Hi,

=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),(SUM(Budget)-SUM(Actual))=0),"",SUM(Budget)-SUM(Actual))
 
Hi

Right click on the PT>Table Options>check Show Error values as, and either
leave as blank or set to 0 as you wish.

--
-------
Regards
Roger Govier

DJL said:
I have entered the following formula within a new calculated item to
calculate the variance between actual and budget values with the view to
making zero values or error values a blank cell. When I insert the
formula
it removes the logic of only displaying the records relevant to the page
setting and I get the same records repeated for each page setting with
#VALUE!

Variance
=IF(OR(ISERROR(SUM(Budget)-SUM(Actual)),SUM(Budget)-SUM(Actual)=0),"",SUM(Budget)-SUM(Actual))

Thanks

__________ Information from ESET Smart Security, version of virus
signature database 4897 (20100226) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4897 (20100226) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4901 (20100227) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
I am still getting the #VALUE! cells. I am wanting the Pivot Table to
exclude these records depending on the page setting as not all row fields
apply to every page setting
 
Unfortunately IFERROR works only in XL2007. The OP probably has XL2003 or
earlier.

Your suggestion of changing "" to 0 is good, though.

Regards,
Fred
 
Thanks, that removes the #VALUE!s but it does not suppress the records that
have no data. It shows ALL the rows on each page but I only want the rows
showing that has data for that page setting. I have checked that all fields
do not have the "show items with no data" selected.
 
Hi

If you want to send me the file, I will take a look
roger at technology4u dot co dot uk
Change the at and dots to make valid email address
 
Back
Top