• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Prevent Pivot table returning zero from table "null" calculation

JohnF123

New Member
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?
 
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?
 
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.
 
Hopefully the attached will help clarify what I'm trying to achieve.

Thank you all for your help.
 

Attachments

  • Pivot table converting blanks to zero.xlsx
    48.3 KB · Views: 17
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.
 
Back
Top