Prevent Pivot table returning zero from table "null" calculation

[JohnF123]

I have a pivot table that is built off an Excel table which has calculation that display a blank cell using the double quotes "" (e.g. IF(ISBLANK(A1), "") ).
When I pivot the Excel table the pivot table returns zero where those blank cells are in the Excel table and there is no way to distinguish between a real calculated zero result and a zero returned from a cell which is blank due to the double quotes.

I've tried generating errors from the calculations and then enabling the 'For error values show:' option in the Pivot Tables Options dialog box to hide the error, but then when I 'Summarize Values By' Average it also returns an error

Is there any way to get the pivot table to display an empty cell when the calculation in the Excel table returns an blank cell and still get the average to calculate?

 

[jeffreyweir]

How about adding another column to the source table called 'Blank' and populating it with a Yes or No, and then filtering out any Yes values in the PivotTable itself?

 

[JohnF123]

Just to clarify, I don't want to filter the blanks out from the view, I want to show them as blanks and not as zeroes and still have the average calculate.

 

[NARAYANK991]

Hi ,

You would probably get an answer fast if you upload your workbook with the data , pivot tables in it.

Narayan

 

[JohnF123]

Hopefully the attached will help clarify what I'm trying to achieve.

Thank you all for your help.

 

[NARAYANK991]

Hi ,

See if this works.

Narayan

 

[JohnF123]

Yes Narayan, that works perfectly. I love the conditional formatting solution.

Thanks.
John

 

[Kiehnej]

Hello Narayan. I'm having the same issue as JonF123 above. Would you be able to share the solution you sent so that null fields stay blank instead of converting to zeros in a pivot table? I think there may be something funny in the underlying data because a portion f my null fields show up as blank and the others populate as zeros.