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

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 "" 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" <[email protected]> wrote in message
news:[email protected]...
> 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?
> --
>
> 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

"JC" <[email protected]> wrote in message
news:[email protected]...
> 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?
> --
>
> 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 "" value.

Many thanks.

On Sun, 8 Jan 2006 14:49:40 -0500, "Jon Peltier"
<[email protected]> 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" <[email protected]> wrote in message
>news:[email protected]...
>> 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?
>> --
>>
>> Cheers . . . JC
>
--

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
http://PeltierTech.com/
_______


"JC" <[email protected]> wrote in message
news:[email protected]...
> 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"
> <[email protected]> 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" <[email protected]> wrote in message
>>news:[email protected]...
>>> 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?
>>> --
>>>
>>> Cheers . . . JC
>>
> --
>
> Cheers . . . JC