Please help with excel chart ignoring text cells

[Goalsquash]

In an XY chart, Excel treats text entered into a cell as having a value = 0.
This 0 value messes with charts because it throws a zero valued point into
the chart trendline. However, blank cells are ignored by XY charts, but I
need to use text to clarify why there is a data gap, e.g., NS = not sampled.

Does anyone know if text can be formatted in Excel so that it isn't assigned
a value, zero or otherwise? Or does anyone have a solution to make the
Excel chart ignore the text cell (ie not plot a zero value)?

Here is an example data set

X Y
1/1/01 3
1/2/02 5
1/4/05 NS
1/28/08 3

Thanks!

[Robin Clay]

I suggest you add an extra column (say column Z, next to
your Y column) bearing the formula

= if IsNumber(Y2,Y2,"")

and use this column in your cahrt

[Jon Peltier]

Write the formula this way to obey the syntax of IF and ISNUMBER:

=IF(ISNUMBER(Y2),Y2,NA())

Also note that I used NA(). Excel ignores the NA(), or #N/A error, when
charting, while it plots "" as a zero. NA() isn't as good as a blank,
because a line chart will interpolate over the gap, while you can tell Excel
to interpolate, skip the point, or plot zero if you have a true blank.
Unfortunately, Excel has no way to return a blank from a formula. Best we
can do is some VBA solution to kill the N/As, as Tushar Mehta does in his
Chart Gap for N/A addin, at http://tushar-mehta.com.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <080101c2d83b$fb08c040$a501...@phx.gbl>, Robin Clay said...

[samieni...@gmail.com]

Dear Jon,

Thank you for your answer.
I am using a macro to set maximum and minimum for "y axis" most probably the one you have shared :)
When i convert to NA, my minimum, maximum which based of a calculation gives NA and macro stops working.
Do you have an alternative solution.

as it is not number, my calculation already ignores the texts, and gives what i want excep the data trend line. Data trend line goes out of the range till zero.
Thanks in advance.