[SOLVED] I want to ignore invalid values in scatter chart

I have an x-y scatter chart which uses values calculated from other sheets in
the workbook. Sometimes these values are invalid, and therefore I use "IF"
to render the appropriate cell blank. The entire series then disappears from
the chart. I just want that point to bu uncharted. Any ideas?

Hi,

I can't reproduce you problem. Depending on the formula used my data
points either do not show, as intended, or have the value zero.
Are you using a formula like this?

=IF( test , value , NA() )

Cheers
Andy

Phil Rowe wrote:
> I have an x-y scatter chart which uses values calculated from other sheets in
> the workbook. Sometimes these values are invalid, and therefore I use "IF"
> to render the appropriate cell blank. The entire series then disappears from
> the chart. I just want that point to bu uncharted. Any ideas?

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Excel cannot "render ... [a] cell blank" via an "IF" function, because
the IF function must return something, and MS has not defined a return
value that would be equivalent to a blank cell. Most people think of
returning "" as blanking the cell, but "" is a string, and Excel graphs
strings as zero. As Andy suggested, NA() or equivalently #N/A will not
plot, but it also does not cause a break in connecting lines on the
chart. Also, #N/A is an error value that will propagate through
formulas unless you filter it out with IF(ISNA()). Bottom line is that
nothing behaves exactly like an empty cell other than a truly empty cell
(i.e. deleting the formula).

Jerry

Phil Rowe wrote:

> I have an x-y scatter chart which uses values calculated from other sheets in
> the workbook. Sometimes these values are invalid, and therefore I use "IF"
> to render the appropriate cell blank. The entire series then disappears from
> the chart. I just want that point to bu uncharted. Any ideas?