software

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($B7gt;0,AVERAGE($B1:$B7),quot;quot;). The line graphs are drawn ok
where there is real data present but at the first point where the quot;quot; 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 quot;quot; 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 quot;quot; as information cells and set up helper
cells off screen with NA() replacing quot;quot; 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

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

quot;JCquot; gt; wrote in message
...
gt; Hi,
gt;
gt; I have several different charts running, both dynamic and static, and both
gt; types
gt; have the same problem.
gt;
gt; Amongst the data being plotted are cells with computed values using
gt; formulas
gt; similar to =IF($B7gt;0,AVERAGE($B1:$B7),quot;quot;). The line graphs are drawn
gt; ok
gt; where there is real data present but at the first point where the quot;quot; is to
gt; be
gt; graphed the line drops to 0. Subsequent points are not plotted but
gt; presumably
gt; would be if 0 was included in the graphing range. I assume that this
gt; happens
gt; because Excel treats text as being zero.
gt;
gt; I have tried replacing the quot;quot; with NA() but this puts #NA in the workbook
gt; cells
gt; and doesn't change the way the graph is presented. If NA() had worked I
gt; would
gt; have left the cells on screen with quot;quot; as information cells and set up
gt; helper
gt; cells off screen with NA() replacing quot;quot; purely to be used for the chart.
gt;
gt; This is not a big problem and I can live with it as it is now but It would
gt; be
gt; better if the graph stopped at the last entry with real data.
gt;
gt; Is there a way to achieve this?
gt; --
gt;
gt; Cheers . . . JC
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 quot;quot; to NA(), then the line will connect the last valid point to
the first quot;quot; 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, quot;#N/Aquot; for instance, then it will behave as quot;quot;.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
PeltierTech.com/
_______quot;JCquot; gt; wrote in message
...
gt; Hi,
gt;
gt; I have several different charts running, both dynamic and static, and both
gt; types
gt; have the same problem.
gt;
gt; Amongst the data being plotted are cells with computed values using
gt; formulas
gt; similar to =IF($B7gt;0,AVERAGE($B1:$B7),quot;quot;). The line graphs are drawn
gt; ok
gt; where there is real data present but at the first point where the quot;quot; is to
gt; be
gt; graphed the line drops to 0. Subsequent points are not plotted but
gt; presumably
gt; would be if 0 was included in the graphing range. I assume that this
gt; happens
gt; because Excel treats text as being zero.
gt;
gt; I have tried replacing the quot;quot; with NA() but this puts #NA in the workbook
gt; cells
gt; and doesn't change the way the graph is presented. If NA() had worked I
gt; would
gt; have left the cells on screen with quot;quot; as information cells and set up
gt; helper
gt; cells off screen with NA() replacing quot;quot; purely to be used for the chart.
gt;
gt; This is not a big problem and I can live with it as it is now but It would
gt; be
gt; better if the graph stopped at the last entry with real data.
gt;
gt; Is there a way to achieve this?
gt; --
gt;
gt; Cheers . . . 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 quot;quot; value.

Many thanks.

On Sun, 8 Jan 2006 14:49:40 -0500, quot;Jon Peltierquot;
gt; wrote:

gt;NA() should in fact change the chart, by causing a line to be interpolated
gt;from one data point, across a gap, to another data point. If you only
gt;changed one quot;quot; to NA(), then the line will connect the last valid point to
gt;the first quot;quot; after the NA(). If all values on one end of a series are NA(),
gt;then the line stops at the last valid data point.
gt;
gt;If you are entering something that only looks like #N/A but is in fact
gt;interpreted as text, quot;#N/Aquot; for instance, then it will behave as quot;quot;.
gt;
gt;- Jon
gt;-------
gt;Jon Peltier, Microsoft Excel MVP
gt;Peltier Technical Services
gt;Tutorials and Custom Solutions
gt;PeltierTech.com/
gt;_______
gt;
gt;
gt;quot;JCquot; gt; wrote in message
.. .
gt;gt; Hi,
gt;gt;
gt;gt; I have several different charts running, both dynamic and static, and both
gt;gt; types
gt;gt; have the same problem.
gt;gt;
gt;gt; Amongst the data being plotted are cells with computed values using
gt;gt; formulas
gt;gt; similar to =IF($B7gt;0,AVERAGE($B1:$B7),quot;quot;). The line graphs are drawn
gt;gt; ok
gt;gt; where there is real data present but at the first point where the quot;quot; is to
gt;gt; be
gt;gt; graphed the line drops to 0. Subsequent points are not plotted but
gt;gt; presumably
gt;gt; would be if 0 was included in the graphing range. I assume that this
gt;gt; happens
gt;gt; because Excel treats text as being zero.
gt;gt;
gt;gt; I have tried replacing the quot;quot; with NA() but this puts #NA in the workbook
gt;gt; cells
gt;gt; and doesn't change the way the graph is presented. If NA() had worked I
gt;gt; would
gt;gt; have left the cells on screen with quot;quot; as information cells and set up
gt;gt; helper
gt;gt; cells off screen with NA() replacing quot;quot; purely to be used for the chart.
gt;gt;
gt;gt; This is not a big problem and I can live with it as it is now but It would
gt;gt; be
gt;gt; better if the graph stopped at the last entry with real data.
gt;gt;
gt;gt; Is there a way to achieve this?
gt;gt; --
gt;gt;
gt;gt; Cheers . . . JC
gt;
--

Cheers . . . JC

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
PeltierTech.com/
_______quot;JCquot; gt; wrote in message
...
gt; John,
gt;
gt; As usual you were spot on. I had only tried changing one to NA() instead
gt; of
gt; all so the line connected to the next quot;quot; value.
gt;
gt; Many thanks.
gt;
gt; On Sun, 8 Jan 2006 14:49:40 -0500, quot;Jon Peltierquot;
gt; gt; wrote:
gt;
gt;gt;NA() should in fact change the chart, by causing a line to be interpolated
gt;gt;from one data point, across a gap, to another data point. If you only
gt;gt;changed one quot;quot; to NA(), then the line will connect the last valid point to
gt;gt;the first quot;quot; after the NA(). If all values on one end of a series are
gt;gt;NA(),
gt;gt;then the line stops at the last valid data point.
gt;gt;
gt;gt;If you are entering something that only looks like #N/A but is in fact
gt;gt;interpreted as text, quot;#N/Aquot; for instance, then it will behave as quot;quot;.
gt;gt;
gt;gt;- Jon
gt;gt;-------
gt;gt;Jon Peltier, Microsoft Excel MVP
gt;gt;Peltier Technical Services
gt;gt;Tutorials and Custom Solutions
gt;gt;PeltierTech.com/
gt;gt;_______
gt;gt;
gt;gt;
gt;gt;quot;JCquot; gt; wrote in message
. ..
gt;gt;gt; Hi,
gt;gt;gt;
gt;gt;gt; I have several different charts running, both dynamic and static, and
gt;gt;gt; both
gt;gt;gt; types
gt;gt;gt; have the same problem.
gt;gt;gt;
gt;gt;gt; Amongst the data being plotted are cells with computed values using
gt;gt;gt; formulas
gt;gt;gt; similar to =IF($B7gt;0,AVERAGE($B1:$B7),quot;quot;). The line graphs are drawn
gt;gt;gt; ok
gt;gt;gt; where there is real data present but at the first point where the quot;quot; is
gt;gt;gt; to
gt;gt;gt; be
gt;gt;gt; graphed the line drops to 0. Subsequent points are not plotted but
gt;gt;gt; presumably
gt;gt;gt; would be if 0 was included in the graphing range. I assume that this
gt;gt;gt; happens
gt;gt;gt; because Excel treats text as being zero.
gt;gt;gt;
gt;gt;gt; I have tried replacing the quot;quot; with NA() but this puts #NA in the
gt;gt;gt; workbook
gt;gt;gt; cells
gt;gt;gt; and doesn't change the way the graph is presented. If NA() had worked
gt;gt;gt; I
gt;gt;gt; would
gt;gt;gt; have left the cells on screen with quot;quot; as information cells and set up
gt;gt;gt; helper
gt;gt;gt; cells off screen with NA() replacing quot;quot; purely to be used for the chart.
gt;gt;gt;
gt;gt;gt; This is not a big problem and I can live with it as it is now but It
gt;gt;gt; would
gt;gt;gt; be
gt;gt;gt; better if the graph stopped at the last entry with real data.
gt;gt;gt;
gt;gt;gt; Is there a way to achieve this?
gt;gt;gt; --
gt;gt;gt;
gt;gt;gt; Cheers . . . JC
gt;gt;
gt; --
gt;
gt; Cheers . . . JC