Jim -

If your test returns NA(), which puts an #N/A error in the cell, the
chart will not be affected, other than by continuing a line across the
gap where you may prefer a gap without a line. Tushar Mehta
(http://tushar-mehta.com) has an addin that takes care of this. Aside
from not leaving a true gap, this is probably the preferred solution.

I suspect your first sequence of blank strings has been cleared,
resulting in truly blank cells. The first cell in the next blank string
region is not, so including it leads to chart damage. Using Paste
Special - Values merely pastes the empty string "" into the target cell,
by the way; but it pastes the blank without the double quotes, so you
need to use ISBLANK() in another cell to know if it's truly blank.
Don't you wish Excel had a BLANK() or NULL() worksheet function that
worked like a blank?

You can select a range and run this macro, and it will clear any cells
which have a formula that returns a text string:

Sub SelectionClearText()
Selection.SpecialCells(xlCellTypeFormulas, 2).ClearContents
End Sub

You could use more sophisticated macros that only clear text cells of
length 1, etc. If you are counting on dynamic formulas, this is not
ideal, since the cleared formulas will no longer update.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jim Kremer wrote:
Thanks for your helpful reply. A followup:

Note that the first range of "blank"cells DOES plot
properly, i.e. keeps the scatter plot mode with values for
the X-axis. When I extend the range to include a cell from
the 2nd range of blank cells, it switches to sequence
count. This seems incosistent.

The blanks WERE created by a conditional test returning "",
but all should have been the same. In addition, I cut the
whole column and PASTED VALUES to a new column the problem
persists.

Q: How can I tell if the offending cell has something or
is indeed empty? That is, if it is filled with "" by a
conditional, how can I tell that vs. a true empty cell?

Finally, this is too big a file, and I have many of them to
process, to deal with these blanks by manually pasting! So,

Q: Is there any way to automate replacing the contents of
cells to be truly empty, similar to a conditional returning
"", that will allow the values to plot properly?


Many thanks.

--Jim



-----Original Message-----
Pursuant to my last response, if there is a nonblank cell

that looks

blank (e.g., a formula that returns "", or a space

character) within the

new X range, it has the same effect on the X axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jim wrote:

When doing an X-Y scatter plot with large block of
contiguous data separated by large gaps with missing data,
the plot suddenly switches from a scatter plot to a line
plot.

That is, with two columns of 50 rows of X & Y, then 50
blank rows, then 50 more rows of X & Y data, the plot is
fine -- a scatter plot with the two sets of data plotted
correctly, lines connecting the points but a gap for the
missing rows.

Then if I extend the range of the series ONE more row into
a blank sectoin, the plot switches from a valid scatter
plot to a line plot (all the Y values plotted against the
numerical count of the rows, ignoring the X values entirely.

This behavior happens on this file with Excel Mac 98,
Mac-X, and Windows, so it seems to be a real bug. It
happens even when the text data are pasted into a new file.

Any ideas? HELP!



.