Need chart to ignore error values, can't use NA solution

[guiismiti]

Hello,

I have a range of 50 cells that are plotted in a scatter chart.
The cells are set to blank "" if the value is an error.
I'm currently using MAX() with the range = the 50 cells, so, I cannot set the value of any of the cells to NA(), or the function will also return NA().
The chart is currently ignoring the "fake" blank cells and their values - it is using {1; 2; 3; 4; 5; ...; 50} as their value, and not the real value or the fake "".
Hidden and empty cells options of the chart are not working (the basic method to ignore the real blanks).
I can't just select the valid range for each case (there are several charts), since this file is gonna be used by other people, and I need to avoid manual work and complications.


Thanks in advance.

[oeldere]

Mabye you could use 0 (zero) instead of "" (blanc cell) in your formula.

[teylyn]

Hello,

Set the error cells not to a blank but to a negative value. Then format the axes of the scatter chart to a minimum value of 0, so the negative plot points won't be showing.

2015-05-30_19-26-51.png

cheers, teylyn

[Glenn Kennedy]

Unless you're using a scatter WITH lines, in which case you could use a helper column (not subject to the MAX function), but including the NA() option.