A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Scatter plot changes to line plot with data gaps



 
 
Thread Tools Display Modes
  #1  
Old September 29th, 2003, 10:10 PM
Jim
external usenet poster
 
Posts: n/a
Default Scatter plot changes to line plot with data gaps

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!


  #2  
Old September 30th, 2003, 05:51 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Scatter plot changes to line plot with data gaps

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, 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!



  #3  
Old September 30th, 2003, 05:53 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Scatter plot changes to line plot with data gaps

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!



  #4  
Old October 1st, 2003, 01:32 PM
Jim Kremer
external usenet poster
 
Posts: n/a
Default Scatter plot changes to line plot with data gaps

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!



.

  #5  
Old October 1st, 2003, 03:56 PM
dvt
external usenet poster
 
Posts: n/a
Default Scatter plot changes to line plot with data gaps

Jim Kremer wrote:
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


  #6  
Old October 1st, 2003, 05:30 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Scatter plot changes to line plot with data gaps

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!



.



  #7  
Old October 1st, 2003, 05:33 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Scatter plot changes to line plot with data gaps


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.


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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

  #8  
Old October 2nd, 2003, 03:42 PM
Jim Kremer
external usenet poster
 
Posts: n/a
Default Scatter plot changes to line plot with data gaps

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?

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. !??

The macro will probably be my solution...



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

Thanks again.



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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

.

  #9  
Old October 2nd, 2003, 05:19 PM
dvt
external usenet poster
 
Posts: n/a
Default Scatter plot changes to line plot with data gaps

Jim Kremer wrote:
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


  #10  
Old October 2nd, 2003, 06:07 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Scatter plot changes to line plot with data gaps


Jim Kremer wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.