XY Scatter doesn't display correctly when data has numbers&empty c

[Guest]

The source data is in columns A (for Y values) and B (for X values). Say A1
through A10 have {6.53, 6.41, 5.89, empty cell, empty cell, empty cell,
empty cell, 6.74, 7.00, 6.50} and B1 through B10 have {7.19, 6.92, 6.74,
6.17, 6.34, 6.57, 6.47, 6.88, 7.12, 6.76}. NOTE that the empty cells result
from an IF statement that if something happens the cell = "". When plotted
as XY Scatter, the chart does not handle properly the empty cells. It
actually becomes like a Line plot, where the X axis is categorical instead of
numerical. I have to CLEAR ALL each of the empty cells so the XY Scatter
works. THe problem is I have thousands of data points and the empty cells
occur randomly throughout the series. Any advice?

 

[Kelly O'Day]

Missing data charting is a frequent user group post.

The key point that I had to learn is that Excel lets you choose how to plot
empty cells: zeros, not plotted or interpolated. Great if a cell is really
empty. For pure data cells, you can select how you want empty cells to be
plotted and you are set.

The problem crops up when you want to plot calculated cells. Your If ""
workaround is a good start, however, Excel sees a formula in your cell so
that to Excel it is not empty, it has a formula in it. it tries to plot your
formula cell, and the best it can do is a 0.

The workaround for calculated cells is to adjust your If formula to
something like:

= If( d2 = "", NA(), b2*d2)

You'll need to replace your actual cell references and formula.

This formula will place a #N/A in those cells that can not be calculated
because of your missing data. Excel recognizes #N/A cells in charting and
ignores them.

I have a tutorial and sample file on this that may be helpful.

http://processtrends.com/pg_charts_missing_data.htm

....Kelly

(e-mail address removed)

 

[Guest]

Many thanks! Problem solved.

Missing data charting is a frequent user group post.

The key point that I had to learn is that Excel lets you choose how to plot
empty cells: zeros, not plotted or interpolated. Great if a cell is really
empty. For pure data cells, you can select how you want empty cells to be
plotted and you are set.

The problem crops up when you want to plot calculated cells. Your If ""
workaround is a good start, however, Excel sees a formula in your cell so
that to Excel it is not empty, it has a formula in it. it tries to plot your
formula cell, and the best it can do is a 0.

The workaround for calculated cells is to adjust your If formula to
something like:

= If( d2 = "", NA(), b2*d2)

You'll need to replace your actual cell references and formula.

This formula will place a #N/A in those cells that can not be calculated
because of your missing data. Excel recognizes #N/A cells in charting and
ignores them.

I have a tutorial and sample file on this that may be helpful.

http://processtrends.com/pg_charts_missing_data.htm

....Kelly

(e-mail address removed)