Forum Discussion

Copper Contributor

Jul 10, 2020

Line charts show zero or #N/A value as zero not as gap even after checking the hidden cell box.

I am trying to create a simple Line chart for and not have zero or #N/A show up on the chart. I have watched a bunch of videos and tried several different formals to hide the points but the still sh...

Formulas and Functions

Iron Contributor

Jul 11, 2020

smherring4 , I can't replicate your issue. Can you upload a sample without any confidential information please?

Copper Contributor

Jul 13, 2020

Here is a sample of what I get.

Line sample no gaps.xlsx20 KB

SergeiBaklan
Diamond Contributor
Jul 13, 2020
smherring4

That's since you have no blank cells. Formula returns empty string, thus the cell has some value. Return NA() instead (you may hide it with conditional formatting or use helper column), upsize and color markers to show separate points if to work with gap option.

=IF(A2<1,NA(),SUM(A2))
Line sample no gaps.xlsx23 KB
- hynguyen
  Iron Contributor
  Jul 14, 2020
  smherring4
  Sergei already explained the reason why you should not use "" in the formula and why you should use NA() instead. As I explained earlier, if you want the line chart to be completely a gap, the cell for that point must be completely blank (i.e. no empty string). To do so, first we need to convert all the points that you want to be a gap to an error by using NA(). Then follow my instructions in Step 3 and 4 to quickly delete those errors so that the cells are completely blank.
  - NikolinoDE
    Platinum Contributor
    Jul 14, 2020
    Repeated the same thing with my excel, and ... oh wonder ... Mr Balkan is right 🙂
    
    I know that I don't know anything ... just try to help with my knowledge of ignorance.
    
    Nikolino

Resources