Line chart zero values

[JC]

Hi,

I have several different charts running, both dynamic and static, and both types
have the same problem.

Amongst the data being plotted are cells with computed values using formulas
similar to =IF($B7>0,AVERAGE($B1:$B7),""). The line graphs are drawn ok
where there is real data present but at the first point where the "" is to be
graphed the line drops to 0. Subsequent points are not plotted but presumably
would be if 0 was included in the graphing range. I assume that this happens
because Excel treats text as being zero.

I have tried replacing the "" with NA() but this puts #NA in the workbook cells
and doesn't change the way the graph is presented. If NA() had worked I would
have left the cells on screen with "" as information cells and set up helper
cells off screen with NA() replacing "" purely to be used for the chart.

This is not a big problem and I can live with it as it is now but It would be
better if the graph stopped at the last entry with real data.

Is there a way to achieve this?

 

[Bernard Liengme]

Try selecting the chart (just click on it) ; use Tools|Option|Chart and set
required option in Plotting Empty Cells box
best wishes--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

 

[Jon Peltier]

NA() should in fact change the chart, by causing a line to be interpolated
from one data point, across a gap, to another data point. If you only
changed one "" to NA(), then the line will connect the last valid point to
the first "" after the NA(). If all values on one end of a series are NA(),
then the line stops at the last valid data point.

If you are entering something that only looks like #N/A but is in fact
interpreted as text, "#N/A" for instance, then it will behave as "".

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

 

[JC]

John,

As usual you were spot on. I had only tried changing one to NA() instead of
all so the line connected to the next "" value.

Many thanks.

NA() should in fact change the chart, by causing a line to be interpolated
from one data point, across a gap, to another data point. If you only
changed one "" to NA(), then the line will connect the last valid point to
the first "" after the NA(). If all values on one end of a series are NA(),
then the line stops at the last valid data point.

If you are entering something that only looks like #N/A but is in fact
interpreted as text, "#N/A" for instance, then it will behave as "".

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

 

[Jon Peltier]

I always try to think: if I'm doing something, and I'm in a hurry, how would
I have messed it up. (Or how do I always mess it up.)

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

John,

As usual you were spot on. I had only tried changing one to NA() instead
of
all so the line connected to the next "" value.

Many thanks.