Forum Discussion
Line charts show zero or #N/A value as zero not as gap even after checking the hidden cell box.
smherring4 , I can't replicate your issue. Can you upload a sample without any confidential information please?
- SergeiBaklanJul 13, 2020Diamond Contributor
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))
- hynguyenJul 14, 2020Iron Contributor
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.
- NikolinoDEJul 14, 2020Gold ContributorRepeated 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