Excluding data points in a chart

Is there some way to specify conditions under which data
points should be excluded in a chart? In particular, I
have two columns for which I want to create a scatter
plot. However, some of the rows are missing data for one
or the other column. Is there a simple way to tell Excel
to ignore such rows?

Jim

Hi Jim,
1) With the chart selected, use Tools|Options and open the Chart tab;
specify what is to happen with missing values
OR
2) Where data is missing enter =NA()
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


"JM" wrote in message
...
Is there some way to specify conditions under which data
points should be excluded in a chart? In particular, I
have two columns for which I want to create a scatter
plot. However, some of the rows are missing data for one
or the other column. Is there a simple way to tell Excel
to ignore such rows?

Jim

Bernard,

Thanks for the tip. However, the data is the result of
an IF function and needs to be used for other results.

Is there a way to return a blank cell from an IF
function? Note that returning "" is not the same as
returning a blank cell because the ToolsOptionsChart
suggetion below only works for truly blank cells.

Your second suggestion (returning =NA()) instead of ""
works for the plot but messes up computations based on
the results. Specifically, I've been unable to get a RANK
() function call to work correctly with #N/A values in
the range provided.

I've tried using array functions to "clean up" the column
with #N/A before applying Rank() but have run into my
usual consternation with array formulas.

So, the best solution is for me to use a blank cell
return if one exists. Does it?

Thanks,
Jim


-----Original Message-----
Hi Jim,
1) With the chart selected, use Tools|Options and open
the Chart tab;
specify what is to happen with missing values
OR
2) Where data is missing enter =NA()
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


"JM" wrote in
message
...
Is there some way to specify conditions under which
data
points should be excluded in a chart? In particular, I
have two columns for which I want to create a scatter
plot. However, some of the rows are missing data for
one
or the other column. Is there a simple way to tell
Excel
to ignore such rows?

Jim


.

How about using a 'helper' column. Use the formula with NA() in this column
and use this column for the plot. Use the formula with "" for other
calculations. The helper column could be hidden or it could be on another
sheet.
To select two columns that are not neighbours for potting, select first
range of data, hold CTRL, select second range of data, click Chart Wizard

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


"JM" wrote in message
...
Bernard,

Thanks for the tip. However, the data is the result of
an IF function and needs to be used for other results.

Is there a way to return a blank cell from an IF
function? Note that returning "" is not the same as
returning a blank cell because the ToolsOptionsChart
suggetion below only works for truly blank cells.

Your second suggestion (returning =NA()) instead of ""
works for the plot but messes up computations based on
the results. Specifically, I've been unable to get a RANK
() function call to work correctly with #N/A values in
the range provided.

I've tried using array functions to "clean up" the column
with #N/A before applying Rank() but have run into my
usual consternation with array formulas.

So, the best solution is for me to use a blank cell
return if one exists. Does it?

Thanks,
Jim


-----Original Message-----
Hi Jim,
1) With the chart selected, use Tools|Options and open
the Chart tab;
specify what is to happen with missing values
OR
2) Where data is missing enter =NA()
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


"JM" wrote in
message
...
Is there some way to specify conditions under which
data
points should be excluded in a chart? In particular, I
have two columns for which I want to create a scatter
plot. However, some of the rows are missing data for
one
or the other column. Is there a simple way to tell
Excel
to ignore such rows?

Jim


.