Greetings:

I am charting data which is not contiguous. If I leave a cell empty,
the chart treats it like a zero (which it isn't). If I drop a #N/A
into the cell, the chart ignores it, which is fine. On the downside,
if there's a #N/A within the base data, AVERAGE() will return #N/A too.

I'd like to have my cake and eat it too. Suggestions?

Thanks;
Duncan

Sorry, one more thing -- please respond to the group, as email to this
address is automatically deleted. Thanks!

Hi Duncan,

Select your chart and look at Tools/Options/Chart and the options for "Plot
empty cells as"

Regards,
Peter T

"Duncan A. McRae" wrote in message
oups.com...
Greetings:

I am charting data which is not contiguous. If I leave a cell empty,
the chart treats it like a zero (which it isn't). If I drop a #N/A
into the cell, the chart ignores it, which is fine. On the downside,
if there's a #N/A within the base data, AVERAGE() will return #N/A too.

I'd like to have my cake and eat it too. Suggestions?

Thanks;
Duncan

Hi Peter, thanks for getting back to me.

In the Options dialogue, it's set to "Plot visible cells only" and
"Plot empty cells as Not Plotted (leave gaps)". In Column C I have the
following formula:

=IF(B310,CONVERT(B31,"lbm","kg"),"-").
or
IF value0 THEN convert value from pounds to kilograms ELSE show
hyphen

Right now, where a "-" exists, the chart is interpreting it as a zero
and dropping lines to the bottom.

OK I follow, AFAIK there's no direct solution to end up displaying "-" for
your zero & -ve values and have the chart ignore them.

Clearly you need the #N/A which you can format to visually appear as a blank
cell, two ways -

Conditional Format:
select the first cell in your list (below assumes it's A1)
Formula Is =ISERROR(A1)
format font to white
copy down (note no $ in the formula)

Custom number format:
[Black]general (change general as required)
Format font in all cells to white

and change your formula to
=IF(B310,CONVERT(B31,"lbm","kg"),#N/A).

Regards,
Peter T

"Duncan A. McRae" wrote in message
ups.com...
Hi Peter, thanks for getting back to me.

In the Options dialogue, it's set to "Plot visible cells only" and
"Plot empty cells as Not Plotted (leave gaps)". In Column C I have the
following formula:

=IF(B310,CONVERT(B31,"lbm","kg"),"-").
or
IF value0 THEN convert value from pounds to kilograms ELSE show
hyphen

Right now, where a "-" exists, the chart is interpreting it as a zero
and dropping lines to the bottom.

OK I follow, AFAIK there's no direct solution to end up displaying "-" for
your zero & -ve values and have the chart ignore them.

What's important to me is the chart -- I don't care for the formatting
of the data at all. I only used #N/A because anything else I've tried
is charted as a zero. When I have values which are (appropriately) in
the 70's and 80's, the chart looks awful when the lines suddenly take a
dive for the x axis. sigh<<<