Scatter plot changes to line plot with data gaps

[Jim]

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!

 

[Jon Peltier]

Jim -

When you extend the data, I suspect the new range contains nonnumeric
data. Any nonnumeric data in the X range will cause the chart to use
the index of the X value, not the value itself.

Make sure the range is not formatted as text, then copy a blank cell,
select the offending cell(s) that contain text, and use Paste Special
from the Edit menu, with the Values and Operation-Add options, to
convert the cells to numbers.

- Jon

 

[Jon Peltier]

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

 

[Jim Kremer]

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

 

[dvt]

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?

In another cell, the ISBLANK function will tell you if that cell is truly
empty.

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?

Not really. You might try to return a #N/A error using the =NA() function
instead of a "". I can't replicate your problem (Win 2000, Excel XP) so I
don't know if it will work like you wish. It might be worth a try.

Another suggestion that might help is one that Jon often proposes. Put a
label in the cell above the y data but *not* above the x data. So if your
data were in A2:B200, put some text in B1 but leave A1 blank. Now select
A1:B200 as your source data.

Dave
dvt at psu dot edu

 

[Jon Peltier]

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]

Not really. You might try to return a #N/A error using the =NA() function
instead of a "". I can't replicate your problem (Win 2000, Excel XP) so I
don't know if it will work like you wish. It might be worth a try.

The problem can be replicated if the upper region of blanks are really
blank, and the lower region has blank strings (""). A VBA solution
could loop through this range and clear out these fake blanks.

Another suggestion that might help is one that Jon often proposes. Put a
label in the cell above the y data but *not* above the x data. So if your
data were in A2:B200, put some text in B1 but leave A1 blank. Now select
A1:B200 as your source data.

While I consider this a good practice, it has no effect on a Scatter
chart. But I do it anyway out of habit.

- Jon

 

[dvt]

I don't understand the *not* entry? Why is it good
practice to put that (or a blank cell?) above the actual
start of the data range?

In short, it makes the Chart Wizard work better. The wizard then recognizes
that the first column is *not* to be charted. Jon may have additional
reasons....

2nd. I jumped at your idea and tried a conditional that
places another cell that is really blank, rather than "",
but to my dismay, it fills them all with zero, not the
contents of that cell. e.g.

= if(N5="",$S$1,I5) places zero in the cell, not the
true blank that is in cell S1.

I don't see why this doesn't work. !??

In my opinion, you are learning that Excel truly does not have a way to
return a blank cell via formula. Based on the things I've read in this
newsgroup, that is a feature that is sorely missed by many people. If
anyone ever comes up with an FAQ for this group, that should probably be the
first one on the list. Sorry that I didn't answer your question directly.

And a curiosity: What is actually IN a cell that has ""
pasted without the quotes? Is it an ASCII char? Something
must really BE there for it to tell the diff from a truly
empty cell...

I don't know.

Dave
dvt at psu dot edu

 

[Jon Peltier]

John & dvt --
You guys are so very helpful. Thanks!!

Glad to help.

I don't understand the *not* entry? Why is it good
practice to put that (or a blank cell?) above the actual
start of the data range?

As mentioned, it doesn't make any difference in a scatter chart, but in
a line (column/area/bar) chart, the blanks unambiguously tell Excel what
to use for series names and category labels.

2nd. I jumped at your idea and tried a conditional that
places another cell that is really blank, rather than "",
but to my dismay, it fills them all with zero, not the
contents of that cell. e.g.

= if(N5="",$S$1,I5) places zero in the cell, not the
true blank that is in cell S1.

I don't see why this doesn't work. !??

Because it puts the "value" of S1 into the returned formula. This is
the coerced value of the blank, which is zero.

Don't worry, you're not crazy. A lot of people have tried exactly these
tricks.

And a curiosity: What is actually IN a cell that has ""
pasted without the quotes? Is it an ASCII char? Something
must really BE there for it to tell the diff from a truly
empty cell...

What's there is a text string, with zero characters. But it's not a
blank, and it's not a null value. Excel knows it's a string, even
though we perceive it as a blank cell.

- Jon

 

[Tushar Mehta]

John & dvt --
You guys are so very helpful. Thanks!!

I don't understand the *not* entry? Why is it good
practice to put that (or a blank cell?) above the actual
start of the data range?

I don't consider it good practice. Columns need headers for any number
of reasons. Just because some moron at MS decided a blank header was
easy to interpret as the x-axis values doesn't make it good practice to
actually do so.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office

 

[Jon Peltier]

If
anyone ever comes up with an FAQ for this group,

Microsoft Excel Charting FAQs
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209

...Gaps in Chart Series; Blanks Chart as Zero
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon020

- Jon

 

[Jon Peltier]

Tushar -

Okay, maybe it's not "good" in terms of worksheet practice, it screws up
filtering, and if you sort, the column is listed as "(Column A)" instead
of something more descriptive. But it's consistent practice in terms of
charting. Most of the time, the header is something like "Date",
"Time", or some category description, so it's self explanatory without
the header anyway. I find it easier to make my chart this way than to
always interact with the Source Data step of the Chart Wizard.

- Jon

 

[Tushar Mehta]

What does 'consistent practice' mean? That if the column contains only
numbers and if the first row is empty then it represents X values?

Would be a heck of a lot better for the chart wizard to have the same
capability as the mini-wizard that adds a new data to a chart('First
column contains x values')!

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Tushar -

The way it used to in Excel 5, you mean? Actually I'd prefer something
like this:

First __ column(s) contain X values
First __ row(s) contain series names

Once I got used to this practice, the blanks provide a convenient way to
specify these rows and columns. It's not perfect, and not ideal, but
I've learned to use it to my advantage.

- Jon