I need to replace 0's with NA

[Jacilyn]

Good morning,

I am looking to update a dynamic getpivotdata table so the results can be charted in a bar chart (a pivot table is used to summarise the getpivotdata table so only relevant cells are reflected in the chart). The issue is that the getpivotdata table includes REF errors that impact on the chart. I have written a formula to say that any REF errors should show as 0 and that is working. However that results in the chart have axis lines for values with 0, this makes the chart too long.

I would like to ensure axis columns with 0 do not display in the chart at all. I believe replacing the 0 with an NA would achieve this (though maybe there is another way). I have tried incorporating the NA function into the formula but am unsure where to place it.

The formula for one line in the getpivotdata table is:

=IFERROR(GETPIVOTDATA("[Measures].[Sum of Hours]",$A$4,"[ActivityRef].[Project&ServiceDetails]","[ActivityRef].[Project&ServiceDetails].&[Asset Data Management]"),0)

Any advice would be greatly appreciated.

 

[Drrellik]

Have you tried to hide the 0's or more correctly do not show zero go to file, then options, advanced, scroll down a little over halfway to Display options and uncheck show zero's

HTH

~DR

 

[Jacilyn]

Have you tried to hide the 0's or more correctly do not show zero go to file, then options, advanced, scroll down a little over halfway to Display options and uncheck show zero's

HTH

~DR

Thanks for taking the time to offer some advice. It removed the zeros in the tables, but I believe they are being treated like blanks so are still showing on the chart.

I read somewhere on google that NA means that they wouldn't appear at all.