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

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?

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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"JoeCars" wrote in message
...
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?

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

What you need for the chart is not 0 or a blank but N/A
Let say you have =IF(A210,A2,"") Replace this by =IF(A20,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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"JoeCars" wrote in message
...
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?