Blanks chart as zeros

#1 04-08-2005, 12:06 PM

Hi All......

I have a Line Chart wherein all cells in my source data that are blank, ( as
result of a formula resulting in ""), all plot as ZERO, and I would like them
to just be nothing, not charted.......I do not want them interpolated and the
one before and the one after just joined......I do not want NA in the cells
because I use them for other formulas.........I go to Tools > Options > Chart
tab, and no matter which option I select under "Plot empty cells as", I STILL
get them plotted as ZERO's......

The only way I've been able to get them to plot correctly is to actually
copy a "real" blank cell over to it......is this the only way, please?

TIA
Vaya con Dios,
Chuck, CABGx3

#2 04-08-2005, 01:06 PM

In your formula, instead of "", put NA().

"CLR" wrote:

> Hi All......
>
> I have a Line Chart wherein all cells in my source data that are blank, ( as
> result of a formula resulting in ""), all plot as ZERO, and I would like them
> to just be nothing, not charted.......I do not want them interpolated and the
> one before and the one after just joined......I do not want NA in the cells
> because I use them for other formulas.........I go to Tools > Options > Chart
> tab, and no matter which option I select under "Plot empty cells as", I STILL
> get them plotted as ZERO's......
>
> The only way I've been able to get them to plot correctly is to actually
> copy a "real" blank cell over to it......is this the only way, please?
>
> TIA
> Vaya con Dios,
> Chuck, CABGx3
>

#3 04-08-2005, 01:06 PM

Thanks for the quick reply Barb, but that don't do it for me........all that
does is put a #N/A in my data column (which comfounds my other formulas) and
then the chart just connects the dots of the cells before and after it,
giving a continuous line..I want a break in the line like you get with a
"real" empty cell.

I forgot to mention I'm using XL97, is 2000 any easier in this regard?

Any other options?

Thanks again,
Vaya con Dios,
Chuck, CABGx3

"Barb R." wrote:

> In your formula, instead of "", put NA().
>
> "CLR" wrote:
>
> > Hi All......
> >
> > I have a Line Chart wherein all cells in my source data that are blank, ( as
> > result of a formula resulting in ""), all plot as ZERO, and I would like them
> > to just be nothing, not charted.......I do not want them interpolated and the
> > one before and the one after just joined......I do not want NA in the cells
> > because I use them for other formulas.........I go to Tools > Options > Chart
> > tab, and no matter which option I select under "Plot empty cells as", I STILL
> > get them plotted as ZERO's......
> >
> > The only way I've been able to get them to plot correctly is to actually
> > copy a "real" blank cell over to it......is this the only way, please?
> >
> > TIA
> > Vaya con Dios,
> > Chuck, CABGx3
> >

#4 04-08-2005, 02:06 PM

Apparently my reply was too quick as I didn't catch everything that you were
looking for. Could you modify the other formulas to include ISNA in them so
that you can deal with NA in this case?

"CLR" wrote:

> Thanks for the quick reply Barb, but that don't do it for me........all that
> does is put a #N/A in my data column (which comfounds my other formulas) and
> then the chart just connects the dots of the cells before and after it,
> giving a continuous line..I want a break in the line like you get with a
> "real" empty cell.
>
> I forgot to mention I'm using XL97, is 2000 any easier in this regard?
>
> Any other options?
>
> Thanks again,
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>
> "Barb R." wrote:
>
> > In your formula, instead of "", put NA().
> >
> > "CLR" wrote:
> >
> > > Hi All......
> > >
> > > I have a Line Chart wherein all cells in my source data that are blank, ( as
> > > result of a formula resulting in ""), all plot as ZERO, and I would like them
> > > to just be nothing, not charted.......I do not want them interpolated and the
> > > one before and the one after just joined......I do not want NA in the cells
> > > because I use them for other formulas.........I go to Tools > Options > Chart
> > > tab, and no matter which option I select under "Plot empty cells as", I STILL
> > > get them plotted as ZERO's......
> > >
> > > The only way I've been able to get them to plot correctly is to actually
> > > copy a "real" blank cell over to it......is this the only way, please?
> > >
> > > TIA
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >

#5 04-08-2005, 02:06 PM

Yes Ma'am, of course I could do that if indeed the N/A thing actually gave me
the "space" I'm looking for, but it doesn't,(at least on my XL97 setup, maybe
it's corrupt?).......the chart just connects the dots from the previous to
the next cells like there was no data point there at all and as tho it's
value was just halfway between the two.....I want a gap.
I've discovered I can use "xxx" to fill in for the "" and then copy
paste-special-values and replace "xxx" with "nothing" and eventually I get it
to where I want, but such a pain.....I guess I could do a macro to do the
whole thing, but is that the only way?

Thanks again,
Chuck, CABGx3

"Barb R." wrote:

> Apparently my reply was too quick as I didn't catch everything that you were
> looking for. Could you modify the other formulas to include ISNA in them so
> that you can deal with NA in this case?
>
> "CLR" wrote:
>
> > Thanks for the quick reply Barb, but that don't do it for me........all that
> > does is put a #N/A in my data column (which comfounds my other formulas) and
> > then the chart just connects the dots of the cells before and after it,
> > giving a continuous line..I want a break in the line like you get with a
> > "real" empty cell.
> >
> > I forgot to mention I'm using XL97, is 2000 any easier in this regard?
> >
> > Any other options?
> >
> > Thanks again,
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> > "Barb R." wrote:
> >
> > > In your formula, instead of "", put NA().
> > >
> > > "CLR" wrote:
> > >
> > > > Hi All......
> > > >
> > > > I have a Line Chart wherein all cells in my source data that are blank, ( as
> > > > result of a formula resulting in ""), all plot as ZERO, and I would like them
> > > > to just be nothing, not charted.......I do not want them interpolated and the
> > > > one before and the one after just joined......I do not want NA in the cells
> > > > because I use them for other formulas.........I go to Tools > Options > Chart
> > > > tab, and no matter which option I select under "Plot empty cells as", I STILL
> > > > get them plotted as ZERO's......
> > > >
> > > > The only way I've been able to get them to plot correctly is to actually
> > > > copy a "real" blank cell over to it......is this the only way, please?
> > > >
> > > > TIA
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >

#6 04-08-2005, 06:06 PM

CLR,

These three resources should help:

From Tushar Mehta:

(1) http://www.tushar-mehta.com/excel/so...discontinuity/

(2)
http://groups-beta.google.com/group/...cca276cbd4877b

From Andy Pope:

(3) http://www.andypope.info/charts/brokenlines.htm

----
Regards,
John Mansfield
http://www.pdbook.com

"CLR" wrote:

> Hi All......
>
> I have a Line Chart wherein all cells in my source data that are blank, ( as
> result of a formula resulting in ""), all plot as ZERO, and I would like them
> to just be nothing, not charted.......I do not want them interpolated and the
> one before and the one after just joined......I do not want NA in the cells
> because I use them for other formulas.........I go to Tools > Options > Chart
> tab, and no matter which option I select under "Plot empty cells as", I STILL
> get them plotted as ZERO's......
>
> The only way I've been able to get them to plot correctly is to actually
> copy a "real" blank cell over to it......is this the only way, please?
>
> TIA
> Vaya con Dios,
> Chuck, CABGx3
>

#7 04-08-2005, 07:06 PM

Thanks for the reply John, but none of those work for me...........I
couldn't get either the first or third ones to work at all and I percieve
that I would have to put code in every chart sheet for the second method to
work and I have too many to do that..........

I do appreciate you trying tho........thanks again,

Vaya con Dios,
Chuck, CABGx3

"John Mansfield" <JohnMansfield@discussions.microsoft.com> wrote in message
news:ED64DEBA-7D39-4EA3-B8A7-C259425F7028@microsoft.com...
> CLR,
>
> These three resources should help:
>
> From Tushar Mehta:
>
> (1) http://www.tushar-mehta.com/excel/so...discontinuity/
>
> (2)
>
http://groups-beta.google.com/group/...rting/msg/31cc
a276cbd4877b
>
> From Andy Pope:
>
> (3) http://www.andypope.info/charts/brokenlines.htm
>
> ----
> Regards,
> John Mansfield
> http://www.pdbook.com
>
>
>
> "CLR" wrote:
>
> > Hi All......
> >
> > I have a Line Chart wherein all cells in my source data that are blank,
( as
> > result of a formula resulting in ""), all plot as ZERO, and I would like
them
> > to just be nothing, not charted.......I do not want them interpolated
and the
> > one before and the one after just joined......I do not want NA in the
cells
> > because I use them for other formulas.........I go to Tools > Options >
Chart
> > tab, and no matter which option I select under "Plot empty cells as", I
STILL
> > get them plotted as ZERO's......
> >
> > The only way I've been able to get them to plot correctly is to actually
> > copy a "real" blank cell over to it......is this the only way, please?
> >
> > TIA
> > Vaya con Dios,
> > Chuck, CABGx3
> >

#8 04-10-2005, 10:06 PM

Nothing behaves exactly like a empty cell, except an empty cell. As you
have discovered, text (even "") plots as zeros, #N/A doesn't plot, but
it also doesn't break joining lines. If you also don't want to kludge a
broken line a la Tushar Mehta and Andy Pope, then your only choice is to
delete the formulas so that the cells will be truly empty.

If the status of these cells will be changing, then you could write a
macro tied to a change event that would delete or replace the formulas
as necessary.

Jerry

CLR wrote:

> Yes Ma'am, of course I could do that if indeed the N/A thing actually gave me
> the "space" I'm looking for, but it doesn't,(at least on my XL97 setup, maybe
> it's corrupt?).......the chart just connects the dots from the previous to
> the next cells like there was no data point there at all and as tho it's
> value was just halfway between the two.....I want a gap.
> I've discovered I can use "xxx" to fill in for the "" and then copy
> paste-special-values and replace "xxx" with "nothing" and eventually I get it
> to where I want, but such a pain.....I guess I could do a macro to do the
> whole thing, but is that the only way?
>
> Thanks again,
> Chuck, CABGx3
>
>
>
>
> "Barb R." wrote:
>
>
>>Apparently my reply was too quick as I didn't catch everything that you were
>>looking for. Could you modify the other formulas to include ISNA in them so
>>that you can deal with NA in this case?
>>
>>"CLR" wrote:
>>
>>
>>>Thanks for the quick reply Barb, but that don't do it for me........all that
>>>does is put a #N/A in my data column (which comfounds my other formulas) and
>>>then the chart just connects the dots of the cells before and after it,
>>>giving a continuous line..I want a break in the line like you get with a
>>>"real" empty cell.
>>>
>>>I forgot to mention I'm using XL97, is 2000 any easier in this regard?
>>>
>>>Any other options?
>>>
>>>Thanks again,
>>>Vaya con Dios,
>>>Chuck, CABGx3
>>>
>>>
>>>
>>>
>>>"Barb R." wrote:
>>>
>>>
>>>>In your formula, instead of "", put NA().
>>>>
>>>>"CLR" wrote:
>>>>
>>>>
>>>>>Hi All......
>>>>>
>>>>>I have a Line Chart wherein all cells in my source data that are blank, ( as
>>>>>result of a formula resulting in ""), all plot as ZERO, and I would like them
>>>>>to just be nothing, not charted.......I do not want them interpolated and the
>>>>>one before and the one after just joined......I do not want NA in the cells
>>>>>because I use them for other formulas.........I go to Tools > Options > Chart
>>>>>tab, and no matter which option I select under "Plot empty cells as", I STILL
>>>>>get them plotted as ZERO's......
>>>>>
>>>>>The only way I've been able to get them to plot correctly is to actually
>>>>>copy a "real" blank cell over to it......is this the only way, please?
>>>>>
>>>>>TIA
>>>>>Vaya con Dios,
>>>>>Chuck, CABGx3

#9 04-10-2005, 10:06 PM

Hi Chuck -

You don't need to put the code into every chart sheet's code module. You
could create a class module, define a variable with events to represent
the chart, and put the code into the class module. Then use a
Worksheet_SheetActivate event to instantiate the newly activated chart
sheet as an instance of this class.

I recently wrote an article about chart events which might help:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

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

CLR wrote:

> Thanks for the reply John, but none of those work for me...........I
> couldn't get either the first or third ones to work at all and I percieve
> that I would have to put code in every chart sheet for the second method to
> work and I have too many to do that..........
>
> I do appreciate you trying tho........thanks again,
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "John Mansfield" <JohnMansfield@discussions.microsoft.com> wrote in message
> news:ED64DEBA-7D39-4EA3-B8A7-C259425F7028@microsoft.com...
>
>>CLR,
>>
>>These three resources should help:
>>
>>From Tushar Mehta:
>>
>>(1) http://www.tushar-mehta.com/excel/so...discontinuity/
>>
>>(2)
>>
>
> http://groups-beta.google.com/group/...rting/msg/31cc
> a276cbd4877b
>
>>From Andy Pope:
>>
>>(3) http://www.andypope.info/charts/brokenlines.htm
>>
>>----
>>Regards,
>>John Mansfield
>>http://www.pdbook.com
>>
>>
>>
>>"CLR" wrote:
>>
>>
>>>Hi All......
>>>
>>>I have a Line Chart wherein all cells in my source data that are blank,
>
> ( as
>
>>>result of a formula resulting in ""), all plot as ZERO, and I would like
>
> them
>
>>>to just be nothing, not charted.......I do not want them interpolated
>
> and the
>
>>>one before and the one after just joined......I do not want NA in the
>
> cells
>
>>>because I use them for other formulas.........I go to Tools > Options >
>
> Chart
>
>>>tab, and no matter which option I select under "Plot empty cells as", I
>
> STILL
>
>>>get them plotted as ZERO's......
>>>
>>>The only way I've been able to get them to plot correctly is to actually
>>>copy a "real" blank cell over to it......is this the only way, please?
>>>
>>>TIA
>>>Vaya con Dios,
>>>Chuck, CABGx3
>>>
>
>
>

#10 04-10-2005, 10:06 PM

Thanks Jerry.........you have very nicely collected and expressed my
thoughts and experiences. I talked with Tushar and he said the Add-in does
not now work with line charts, (since XL2k), so thats out. The task I have
at hand is to generate about 30 charts for Machine Efficiency.....line
charts done weekly, above and below Standard efficiency, and I believe they
only have to exist long enough to print them out, so a changing of the Data
Source should be quite acceptable......I'll just whip up a macro to empty
out the cells and print the chart and then delete it so values can't be
changed and someone wondering why the chart don't respond.........I've done
it on samples and it seems to work just fine.

Thanks very much for your response.

Vaya con Dios,
Chuck, CABGx3

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:4259BFCE.7030409@no_e-mail.com...
> Nothing behaves exactly like a empty cell, except an empty cell. As you
> have discovered, text (even "") plots as zeros, #N/A doesn't plot, but
> it also doesn't break joining lines. If you also don't want to kludge a
> broken line a la Tushar Mehta and Andy Pope, then your only choice is to
> delete the formulas so that the cells will be truly empty.
>
> If the status of these cells will be changing, then you could write a
> macro tied to a change event that would delete or replace the formulas
> as necessary.
>
> Jerry
>
> CLR wrote:
>
> > Yes Ma'am, of course I could do that if indeed the N/A thing actually
gave me
> > the "space" I'm looking for, but it doesn't,(at least on my XL97 setup,
maybe
> > it's corrupt?).......the chart just connects the dots from the previous
to
> > the next cells like there was no data point there at all and as tho it's
> > value was just halfway between the two.....I want a gap.
> > I've discovered I can use "xxx" to fill in for the "" and then copy
> > paste-special-values and replace "xxx" with "nothing" and eventually I
get it
> > to where I want, but such a pain.....I guess I could do a macro to do
the
> > whole thing, but is that the only way?
> >
> > Thanks again,
> > Chuck, CABGx3
> >
> >
> >
> >
> > "Barb R." wrote:
> >
> >
> >>Apparently my reply was too quick as I didn't catch everything that you
were
> >>looking for. Could you modify the other formulas to include ISNA in
them so
> >>that you can deal with NA in this case?
> >>
> >>"CLR" wrote:
> >>
> >>
> >>>Thanks for the quick reply Barb, but that don't do it for me........all
that
> >>>does is put a #N/A in my data column (which comfounds my other
formulas) and
> >>>then the chart just connects the dots of the cells before and after it,
> >>>giving a continuous line..I want a break in the line like you get with
a
> >>>"real" empty cell.
> >>>
> >>>I forgot to mention I'm using XL97, is 2000 any easier in this regard?
> >>>
> >>>Any other options?
> >>>
> >>>Thanks again,
> >>>Vaya con Dios,
> >>>Chuck, CABGx3
> >>>
> >>>
> >>>
> >>>
> >>>"Barb R." wrote:
> >>>
> >>>
> >>>>In your formula, instead of "", put NA().
> >>>>
> >>>>"CLR" wrote:
> >>>>
> >>>>
> >>>>>Hi All......
> >>>>>
> >>>>>I have a Line Chart wherein all cells in my source data that are
blank, ( as
> >>>>>result of a formula resulting in ""), all plot as ZERO, and I would
like them
> >>>>>to just be nothing, not charted.......I do not want them interpolated
and the
> >>>>>one before and the one after just joined......I do not want NA in the
cells
> >>>>>because I use them for other formulas.........I go to Tools > Options
> Chart
> >>>>>tab, and no matter which option I select under "Plot empty cells as",
I STILL
> >>>>>get them plotted as ZERO's......
> >>>>>
> >>>>>The only way I've been able to get them to plot correctly is to
actually
> >>>>>copy a "real" blank cell over to it......is this the only way,
please?
> >>>>>
> >>>>>TIA
> >>>>>Vaya con Dios,
> >>>>>Chuck, CABGx3
>

#11 04-10-2005, 10:06 PM

Thanks Jon but that's a bit over my head. I've no experience with Class
Modules, and need to get this thing working soon, so I will probably go with
the "delete the formulas" method Jerry mentioned........I do appreciate your
response tho, I go to your page frequently and have learned a LOT
there........I think I'm going to be trying a Speedometer Chart
soon........I'll also check out the article you mentioned...........

Thanks again,
Vaya con Dios,
Chuck, CABGx3

"Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
news:O$o8ptiPFHA.2384@tk2msftngp13.phx.gbl...
> Hi Chuck -
>
> You don't need to put the code into every chart sheet's code module. You
> could create a class module, define a variable with events to represent
> the chart, and put the code into the class module. Then use a
> Worksheet_SheetActivate event to instantiate the newly activated chart
> sheet as an instance of this class.
>
> I recently wrote an article about chart events which might help:
>
> http://www.computorcompanion.com/LPMArticle.asp?ID=221
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> CLR wrote:
>
> > Thanks for the reply John, but none of those work for me...........I
> > couldn't get either the first or third ones to work at all and I
percieve
> > that I would have to put code in every chart sheet for the second method
to
> > work and I have too many to do that..........
> >
> > I do appreciate you trying tho........thanks again,
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "John Mansfield" <JohnMansfield@discussions.microsoft.com> wrote in
message
> > news:ED64DEBA-7D39-4EA3-B8A7-C259425F7028@microsoft.com...
> >
> >>CLR,
> >>
> >>These three resources should help:
> >>
> >>From Tushar Mehta:
> >>
> >>(1) http://www.tushar-mehta.com/excel/so...discontinuity/
> >>
> >>(2)
> >>
> >
> >
http://groups-beta.google.com/group/...rting/msg/31cc
> > a276cbd4877b
> >
> >>From Andy Pope:
> >>
> >>(3) http://www.andypope.info/charts/brokenlines.htm
> >>
> >>----
> >>Regards,
> >>John Mansfield
> >>http://www.pdbook.com
> >>
> >>
> >>
> >>"CLR" wrote:
> >>
> >>
> >>>Hi All......
> >>>
> >>>I have a Line Chart wherein all cells in my source data that are blank,
> >
> > ( as
> >
> >>>result of a formula resulting in ""), all plot as ZERO, and I would
like
> >
> > them
> >
> >>>to just be nothing, not charted.......I do not want them interpolated
> >
> > and the
> >
> >>>one before and the one after just joined......I do not want NA in the
> >
> > cells
> >
> >>>because I use them for other formulas.........I go to Tools > Options >
> >
> > Chart
> >
> >>>tab, and no matter which option I select under "Plot empty cells as", I
> >
> > STILL
> >
> >>>get them plotted as ZERO's......
> >>>
> >>>The only way I've been able to get them to plot correctly is to
actually
> >>>copy a "real" blank cell over to it......is this the only way, please?
> >>>
> >>>TIA
> >>>Vaya con Dios,
> >>>Chuck, CABGx3
> >>>
> >
> >
> >

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)