Line chart zero values
 

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($B70,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?
--

Cheers . . . JC

Line chart zero values
 

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

"JC" wrote in message
...
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($B70,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?
--

Cheers . . . JC

Line chart zero values
 

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" wrote in message
...
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($B70,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?
--

Cheers . . . JC

Line chart zero values
 

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.

On Sun, 8 Jan 2006 14:49:40 -0500, "Jon Peltier"
wrote:

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" wrote in message
.. .
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($B70,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?
--

Cheers . . . JC

--

Cheers . . . JC

Line chart zero values
 

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/
_______


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

On Sun, 8 Jan 2006 14:49:40 -0500, "Jon Peltier"
wrote:

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" wrote in message
. ..
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($B70,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?
--

Cheers . . . JC

--

Cheers . . . JC