Problem with Chart drawing blank cells as zero

[Mat]

Hi

I am currently trying to create an excel line chart showing cumulative
baseline vs Cumulative Actual vs forecast actual lines (the forecast line is
a continuation of the Actual to date line) which I have done - but am trying
to automate it so it is 'more user friendly' and can be created at the touch
of a macro.
The problem I am having is that one of the formulae i am using looks up a
date in the timescale and if it is in the future I want it to leave the cell
bank (using the "" option" - problem is the chart then decides this is not a
blank cell it is a zero and plots it accordingly. - Obviously I can "clear
the contents" afterwards to put the chart back to where it should be - but is
there another option ?
Please help -all comments appreciated - is there any VB that can sort this ?

Thanks in advance

Mat

 

[Andy Pope]

Hi,

Use NA() instead of "".
Or perhaps consider named ranges to only plot as far as is required.
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

 

[Jon Peltier]

Mat -

A cell with a formula by definition is not blank. Even if it returns "",
that's not blank, that's just a short bit of text.

The best you can do for an XY or line chart is to change "" to NA() in your
formula. This returns the unattractive #N/A error in the cell, but a chart
will not plot a marker for this point. If your markers have lines connecting
them, the line will connect the points on either side of the #N/A. This
means you will not get a gap in the line. There are some heroic measures you
can take to display an apparent gap, see Andy Pope's web site for an
example, http://andypope.info/.

We have asked Microsoft to add a worksheet formula like BLANK() or NULL(),
but they have not yet obliged us.

- Jon