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

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?

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

[email protected]





"cacpanama" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

Many thanks! Problem solved.

"Kelly O'Day" wrote:

> 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
>
> [email protected]
>
>
>
>
>
> "cacpanama" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> >
>
>
>