Excel charts...how do I create a true blank cell?

tomstickland

I want to use a single series to plot a series of broken lines.
In previous versions of Excel I used a formula to put a blank string into empty cells.

So I could draw two horizontal lines with the following two columns as x and y data for a scatter plot:
1, 1
2, 1
3 , ""
1, 2
2, 3

I'm now using Excel 2003, and it forces the blank cell to be a zero. I've tried all of the options (don't plot empty cells) etc.
However, my formula has created a blank cell, not an empty cell.

Is there anything that I can put into the cell via a formula that will stop it trying to plot that cell? I tried "#N/A" but that meant it skipped that row altogether.


Edit:
OK, looks like loads of people have had this problem too.
If I check the cell with "=Isblank(cell ref)" in another cell
then only deleting the contents makes it blank.
Setting it to "" does not.
Setting it to "#N/A" or "Na()" does not.

It needs a "Null()" or "Blank()" function.

http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2007-02/msg01748.html

There is unfortunately no function that returns a blank. We would like
to
have something like BLANK() or NULL(), but it's not available.

=IF(LEN(A1)=0,NA(),A1)

LEN(A1) checks for a blank. If there is a blank, NA() turns into #N/A
in the
chart, which isn't exactly a blank: no point is drawn in a line or XY
chart
for this cell, and any line connecting data points connects the points
on
either side.

- Jon