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

Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data is to
be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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

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().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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

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

"Jobe" wrote in message
...
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().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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

Thank you

"David Biddulph" wrote:

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

"Jobe" wrote in message
...
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().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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

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

"David Biddulph" wrote:

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

"Jobe" wrote in message
...
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().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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

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

"Jobe" wrote:

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

"David Biddulph" wrote:

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

"Jobe" wrote in message
...
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().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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

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.
--
David Biddulph

"Jobe" wrote in message
...
Do I have to do this on a cell by cell basis

"David Biddulph" wrote:

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

"Jobe" wrote in message
...
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().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing
data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jobe" wrote in message
...
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().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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

On Wed, 16 May 2007, in microsoft.public.excel.charting,
David Biddulph said:
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.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
On Wed, 16 May 2007, in microsoft.public.excel.charting,
David Biddulph said:
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.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

On Wed, 16 May 2007, in microsoft.public.excel.charting,
Jon Peltier said:

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.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
On Wed, 16 May 2007, in microsoft.public.excel.charting,
Jon Peltier said:

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.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

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

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jobe" wrote in message
...
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().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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

On Wed, 16 May 2007, in microsoft.public.excel.charting,
Jon Peltier said:

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.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


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

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jobe" wrote in message
...
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().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing
data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
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