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