Blank Values in Excel Charts
 

I'm creating a chart from a column of numerical values. Several of the cells
in the column are blank (=" " rather than having them display an unstable
value which they would be otherwise). When this set of data is transferred to
an Excel chart the cells with the " " value are charted as if the cells have
the value of zero. How do I exclued these entries from being included in the
charts without affecting the desired data? I would like the blank values to
show an empty spot in the chart rather than a value of zero.
--
Ken

Blank Values in Excel Charts
 

Ken.

Make you blanks which I assume are formula generated evaluate as NA()
instead of blank and Excel won't chart them it will interpolate between
adjacent values in the chart.

Mike

"Ken" wrote:

I'm creating a chart from a column of numerical values. Several of the cells
in the column are blank (=" " rather than having them display an unstable
value which they would be otherwise). When this set of data is transferred to
an Excel chart the cells with the " " value are charted as if the cells have
the value of zero. How do I exclued these entries from being included in the
charts without affecting the desired data? I would like the blank values to
show an empty spot in the chart rather than a value of zero.
--
Ken

Blank Values in Excel Charts
 

Hi,

All non numeric values in cells will be treated as text and therefore
charted as zero.
The exception is the use of the formula NA() which will suppress the
plotting of a data marker. In a line chart the line will be joined
between 2 valid data points.
The only way to get a line break is to have truly empty cells.

Cheers
Andy

Ken wrote:
I'm creating a chart from a column of numerical values. Several of the cells
in the column are blank (=" " rather than having them display an unstable
value which they would be otherwise). When this set of data is transferred to
an Excel chart the cells with the " " value are charted as if the cells have
the value of zero. How do I exclued these entries from being included in the
charts without affecting the desired data? I would like the blank values to
show an empty spot in the chart rather than a value of zero.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Blank Values in Excel Charts
 

Hi,

Replace formulas like
=IF(A3<10," ",A3)
with
=IF(A3<10,NA(),A3)

A couple of points:
1. It is probably better technique to use "" to represent a blank cell
rather than " ". In many situations " " would not be treated as a blank
cell, but "" would.
2. In a column or bar chart this technique works very well, however, in a
line chart if your goal was to leave a break in the line, this would not do
it, because as Mike has said Excel interpolates (connects the known points as
thought the missing point was on the line connecting them).
3. You should look at Jon Peltier's website for other useful charting
suggestions:
http://peltiertech.com/Excel/Charts/
--
Thanks,
Shane Devenshire


"Ken" wrote:

I'm creating a chart from a column of numerical values. Several of the cells
in the column are blank (=" " rather than having them display an unstable
value which they would be otherwise). When this set of data is transferred to
an Excel chart the cells with the " " value are charted as if the cells have
the value of zero. How do I exclued these entries from being included in the
charts without affecting the desired data? I would like the blank values to
show an empty spot in the chart rather than a value of zero.
--
Ken

Blank Values in Excel Charts
 

This idea works good, however, if you are doing any calculations in your
spreadsheet such as finding the average, you get an error. Is there anyway
to have the best of both worlds.



"ShaneDevenshire" wrote:

Hi,

Replace formulas like
=IF(A3<10," ",A3)
with
=IF(A3<10,NA(),A3)

A couple of points:
1. It is probably better technique to use "" to represent a blank cell
rather than " ". In many situations " " would not be treated as a blank
cell, but "" would.
2. In a column or bar chart this technique works very well, however, in a
line chart if your goal was to leave a break in the line, this would not do
it, because as Mike has said Excel interpolates (connects the known points as
thought the missing point was on the line connecting them).
3. You should look at Jon Peltier's website for other useful charting
suggestions:
http://peltiertech.com/Excel/Charts/
--
Thanks,
Shane Devenshire


"Ken" wrote:

I'm creating a chart from a column of numerical values. Several of the cells
in the column are blank (=" " rather than having them display an unstable
value which they would be otherwise). When this set of data is transferred to
an Excel chart the cells with the " " value are charted as if the cells have
the value of zero. How do I exclued these entries from being included in the
charts without affecting the desired data? I would like the blank values to
show an empty spot in the chart rather than a value of zero.
--
Ken