Blanks chart as zeros

[CLR]

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

 

[Barb R.]

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

 

[CLR]

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.]

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]

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

 

[John Mansfield]

CLR,

These three resources should help:

From Tushar Mehta:

(1) http://www.tushar-mehta.com/excel/software/na_discontinuity/

(2)
http://groups-beta.google.com/group/microsoft.public.excel.charting/msg/31cca276cbd4877b

From Andy Pope:

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

 

[CLR]

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

 

[Jerry W. Lewis]

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

 

[Jon Peltier]

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]

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

 

[CLR]

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