plotting "empty" cells that contain a formula that has returned a blank

[bob farey]

Cells that appear empty, but contain a formula that has
returned a blank, when plotted on a line chart, plot as
ZEROS. This is in spite of setting tools, options, charts
to "zero values not plotted". Does anyone know a way round
this, please.

thanks
Bob Farey

 

[Tushar Mehta]

Instead of return a blank (" " or "") result, return a NA().

If you need a null string ("") for subsequent calculations or for
aesthetic purposes, create a 2nd dummy range that has NA() instead of
"".

Plot the range with the NA()s

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[bobf]

The only work round I have is to run a macro that converts
all formulas to values, then the "empty" cells do not
plot. Of course, I then have to run another macro to re-
instate the formulas. I attach these macros to 2 boxes
adjacent to the chart, and label them "click to suppress
plotting of empty cells" and "click to re-instate formulas"

-----Original Message-----
Bob, I have the very same problem and I have spent too

many hours in help to count trying to figure out a work
around. According to help if you enter "" in a
conditional formula this should work however it does not.
I have make the changes to how the chart sees a blank cell
through options but that does not work. I join you in
requesting someone help in solving this problem. However
it is nice to know that I am not the only person with this
issue. Thank you, Les.

 

[Jon Peltier]

Bob -

Did you happen to read Tushar's post? Much easier than running two
macros all the time.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[JImCC]

Yes the NA()does work.
But is therea way for the series line to be broken.
for example:
I am trending a series over a priod of time say day 1 to
4. on the 3 day there is no data.
I would like the series to have no data there and the x-
axis still to have the date day 3.
Is this possible. Using NA() conects the series line from
day 2 to day 4?

Hopeful?

 

[Guest]

Jon,
But that still leaves me with a line crossing the gap, I
need a break in the chart. Unless I have not fully
understood his answer, of course.

Bob

 

[Jon Peltier]

Bob -

Yes, it leaves a line spanning the gap. There are a few things you can
do for this. Manually change the line formatting for that point,
manually clearing the cell with the formula, or programmatically doing
these things; unfortunately if the data changes, you would have to
reinstate the formula or reformat the line. Tushar Mehta has a
workaround on his web site (http://tushar-mehta.com, look for keywords
chart and N/A on the TOC), as does Andy Pope (http://andypope.info).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______