Avoid plotting refferanced blanks as zeros in scatter plot graphs

[Jobe]

I can not figure out how not to plot data that referances blank cells as
zero's. I have a worksheet that links to tables with data orginized by size
(mm) from big to small. This worksheet contains a scatter plot graph that
looks at all sizes in the table. Typically not all sizes have data. The
only way I can figure out to remove the zero values from the blank cells is
by manualy selecting these cells and clearing the content. I have tried
several attempts at differant logic ( if(isnumber(A1),A1,""), and
if(isblank(A1),"",A1) ect. and nothing seems to work. I could use
conditional formating but I would like to keep things as simple as possible

 

[Bernard Liengme]

Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data is to
be treated
best wishes

 

[Jon Peltier]

If the "blank" is the result of "" in a formula, Tools menu > Options >
Chart tab > 'Plot Empty Cells As' will have no effect.

- Jon

 

[Bernard Liengme]

Agreed! so use NA() in place of "" in the formula
best wishes

 

[Jobe]

Thank you guys for your input, NA() does work for not plotting zero values
but now my table is is littered with #N/As. Is it possible to have a to
eliminate this display of the #N/As from the table. Otherwise I will make a
hidden table with NA().

 

[David Biddulph]

Format/ Conditional formatting/ Formula is/ =ISNA(A1)
Set font colour to the same as the background colour.

 

[Jobe]

Thank you

Format/ Conditional formatting/ Formula is/ =ISNA(A1)
Set font colour to the same as the background colour.

 

[Jobe]

Do I have to do this on a cell by cell basis

Format/ Conditional formatting/ Formula is/ =ISNA(A1)
Set font colour to the same as the background colour.

 

[Jobe]

Nevermind, I just had to remove the dollar signs. Thanks againJon, Bernard,
and David

 

[David Biddulph]

Either select all the cells (and ensure that A1 is the active cell when you
enter the formula ), or format the one cell and copy format.

 

[Jon Peltier]

Since worksheets don't cost much, I generally make one table for each use I
have for the data, all linked back to the original data. There could be
several sheets: one for the original data, one for the chart source data,
one for optimal screen viewing, one (or more) optimized to print, one or
more optimized to export to Word or PowerPoint. The chart one shows the
#N/A, and it's useful to show these; the ones for display do not. The ones
for display might skip rows or columns and have fancy borders or shading to
make them easy to read, the one for the chart does not. It's just so much
easier to do each table individually than to try to figure out how the same
table will work for different functions that have different requirements.

- Jon

 

[Del Cotter]

Format/ Conditional formatting/ Formula is/ =ISNA(A1)
Set font colour to the same as the background colour.

Alternatively, using the custom number format

General;;;

also works, as suggested by tmirelle in a previous post

http://groups.google.com/group/
microsoft.public.excel.charting/msg/8d3ae44f522983f4

The funny thing is that, now I've tested it, the reason why I never
found it by myself is clear. I was testing it on cells, and in cells
that format does *not* obscure the #N/A. But it does in the chart.
Weird!

Also, although it works fine in line and scatter charts, it produced a
very strange result in a bar chart. The actual "#N/A" text appeared
superimposed on the label of the previous data point, and the label
frame associated with that text was way up in the top left hand corner.
Very buggy.

 

[Jon Peltier]

I just tested this (Excel 2003 SP2) and discovered that

<format>;;;

does not prevent display of #N/A in the chart (data labels showing values of
points) nor in the sheet. If what you have is text that looks like the
error, such as '#N/A, then the format hides this label in the chart and in
the sheet, because the format for text (after the third is blank.

This is why NA() works for charts with markers (XY, line, and radar),
because points (markers) are not plotted for #N/A (and therefore the labels
do not show), while "" works for column or bar charts, because the
zero-thickness column/bar doesn't show, and neither does the "" label.

- Jon

 

[Del Cotter]

This is why NA() works for charts with markers (XY, line, and radar),
because points (markers) are not plotted for #N/A (and therefore the labels
do not show), while "" works for column or bar charts, because the
zero-thickness column/bar doesn't show, and neither does the "" label.

I must now slap my forehead as I did when tmirelle first made the
suggestion, for the opposite reason this time: that it never did work
after all, it just looked like it worked because the points didn't show.

It's still funny about the labels and label frames going buggy all over
the place with bars for me though.

I guess the setup you described in your previous post is the only way to
go: mirrored sheets, one formatted for publication as a table, one
formatted for feeding a chart, etc. A counterintuitive case of the
"helper column" principle for preparing spreadsheet data for charting,
in a situation where one wouldn't think it necessary.

 

[Jon Peltier]

If 'Keep It Simple' means use an extra sheet, then use an extra sheet. I
can't believe how many people are so reluctant to do so even when the
benefits are explained.

- Jon

 

[mac]

I need some help with the problem. I tried this in my formula which is
=IF(V7+W7>0,V7+W7,NA()). This does not work for my bar chart. The chart still
shoes my value as Zero. Why is that?
Mac

 

[Del Cotter]

If 'Keep It Simple' means use an extra sheet, then use an extra sheet. I
can't believe how many people are so reluctant to do so even when the
benefits are explained.

I sympathise to some extent; there's a human instinct to thriftiness
that doesn't deal with computer "space" very well. An excellent example
of this is my company (and other companies) who periodically commit vast
numbers of paid company man-minutes every year to the project of
clearing old email out of the servers, when the price of computer
storage nowadays is such that simply buying more space would be much
cheaper. But you can't convince the budget holders of that.

And then again, there's an instinct that mimics the reluctance to
duplicate records in databases, for fear that the duplicates could fall
out of sync. The answer to that is the relational database, that holds
one unique source of each bit of information and feeds many instances
where that bit needs to be displayed. Users want the table and the graph
to directly read the same cell, lest their table and their graph
accidentally end up telling different stories.

But good spreadsheet design is easily able to achieve the same goal even
when cells have been duplicated "unnecessarily": they will eventually
all lead back to the ur-source, and there's no inevitable reason why the
helper cells should be hard to sense-check for that goal.

 

[Jon Peltier]

The NA() or #N/A only prevents plotting of a marker (in an XY, line, or
radar chart). Otherwise it is plotted as a bar of zero thickness, either on
the category axis or in its place in the order of stacked series. It is not
really a value of zero, as a text entry would be; if the category axis
crosses at a Y value other than zero (bad practice for a bar or column
chart, by the way), the Excel-interpreted zero value of the text will result
in a bar or column from the category axis to zero on the Value axis. The
#N/A does not result in such a zero-seeking bar.

- Jon