I need to replace 0's with NA

Jacilyn

New Member
Joined
Nov 5, 2019
Messages
3
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.
 

Attachments

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
747
Office Version
365, 2016, 2013, 2011, 2010
Platform
Windows
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

New Member
Joined
Nov 5, 2019
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,503
Messages
5,468,994
Members
406,627
Latest member
IncandenzaH1997

This Week's Hot Topics

Top