how do I get the if function to return a blank cell, not 0?

[JoeCars]

I am trying to create a chart from a series that contains data for each
month. The series is calculated on other worksheets and copied to the
worksheet containing the chart. I would like to have the cells for the
months that have not been updated yet (now is January, there are 0' in all
cells for Feb-Dec) to be blank (to create gaps in the chart) not 0's.
Can this be done?

 

[Jon Peltier]

Unfortunately, what you want, and what many of us have requested but doesn't
exist, is a worksheet function like BLANK() or NULL(). The best we can do is
use NA() in a chart's data source, which isn't interpreted as a zero by
marker-type charts (XY, Line, and I think the line & marker style radar
plots). So...

=IF(condition,value,NA())

You get an ugly #N/A error in the sheet, but it can be hidden with
conditional formatting. See Debra's explanation:

http://contextures.com/xlCondFormat03.html#Errors

- Jon

 

[Bernard Liengme]

What you need for the chart is not 0 or a blank but N/A
Let say you have =IF(A2>10,A2,"") Replace this by =IF(A2>0,A2,NA())
You will see #N/A in cells when the A value is <10. The chart engine will
ignore these values
Don't like the appearance of you table? Use Conditional Format to hide the
#N/A
best wishes