Problem with Chart drawing blank cells as zero
 

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

Problem with Chart drawing blank cells as zero
 

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Mat" wrote in message
...
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

Problem with Chart drawing blank cells as zero
 

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


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