Showing blank field within pivot table

Hello all,
I have a question concerning pivot tables. I had a pivot table that I would refresh monthly. It would show all of the months for the entire year, but there would only be values within the months that had data. I was originally pulling the values from a calculation I was doing within the data file. Recently my boss went in and changed the pivot table to perform the percentage calculation in the pivot table rather than grabbing the value from the data source. Now, I have #DIV/0 for the months of Oct-Dec, because there aren't any values for those months within my data tab. Does anyone know how I can keep the formula within the pivot, and still be able to show the full year without having Oct-Dec show #DIV/0? I have attached an example of the pivots to show you what I am talking about. Thanks for any and all help!

Damian37

In your Pivot Table Tools Options contextual ribbon, click the "Fields, Items, & Sets" button and select "Calculated Field".

In the "Name" dropdown, select the calculated field your manager created.

Wrap that formula in IFRROR() (eg =IFERROR(ManagersFormula,"" )

Originally Posted by Whizbang

In your Pivot Table Tools Options contextual ribbon, click the "Fields, Items, & Sets" button and select "Calculated Field".

In the "Name" dropdown, select the calculated field your manager created.

Wrap that formula in IFRROR() (eg =IFERROR(ManagersFormula,"" )

Thanks Whizbang,
I tried updating the formula with your suggestion: =IFERROR( 1-'No Adjustment 2'/'Net Items Counted',""). Now I am receiving #VALUE instead of #DIV/0. Am I missing a zero somewhere? Also, the #VALUE is replacing any of the values that originally came up #DIV/0. There are some areas within the pivot for previous months that should appear as #DIV/0. This update seems to affect not only future months, but also prior months as well.

Hmmm. It might be that the "" is text and the field is expecting a number.

Change the formula to either of these:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

You might need to play with it to get the desired result.

Other options include not showing 0 values or not showing error values. Both are in the workbook Options.

[Edit] Just reread your post. If you want to show some errors but not others, you'll probably have to do something like this:

Please Login or Register  to view this content.