Excluding data points in a chart

[JM]

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 Liengme]

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()

 

[JM]

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 Tools>Options>Chart
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

 

[Bernard Liengme]

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