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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|