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

  • Chart with unecessary axis titles.PNG
    Chart with unecessary axis titles.PNG
    29.1 KB · Views: 5
  • Clip of getpivotdata table.PNG
    Clip of getpivotdata table.PNG
    12.7 KB · Views: 5
  • Clip of Pivot Table included unwater lines.PNG
    Clip of Pivot Table included unwater lines.PNG
    10.9 KB · Views: 4
  • Clip of getpivotdata table.PNG
    Clip of getpivotdata table.PNG
    12.7 KB · Views: 3
  • Clip of Pivot Table included unwater lines.PNG
    Clip of Pivot Table included unwater lines.PNG
    10.9 KB · Views: 3
  • Chart with unecessary axis titles.PNG
    Chart with unecessary axis titles.PNG
    29.1 KB · Views: 4

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
770
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. 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,130,079
Messages
5,639,941
Members
417,119
Latest member
adityaj252

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top