plotting column with many empty boxes

**Jeannot** · 05-15-2009, 05:45 AM

Good morning everyone,

I have a long data set and I want to plot a column against another one.
Column one is filled in at every line, while in column 2 is only partly filled in:
in most cells of column 2 there is nothing.

If I plot column 2 against column 1, excel interpret that column 2 equal 0 when the cell is empty, while I want him to plot no point...

How can I force him to plot no point?

Thanks for your help

**Andy Pope** · 05-15-2009, 05:47 AM

can you post example

Are the cells really empty?
what type of chart are you using?

**Jeannot** · 05-15-2009, 06:46 AM

Hey

It is true, the cell is not really empty, there is a formula:

=IF($AE16<30,IF($AE16>0,$L16,""),"")
And we are in the situation where it the formula returns "" obviously.

I want a normal scatter plot and I would like Excel to plot a trend on this scatter. I get what is shown in the attached picture, so all the point on the x-axis bother me.

Thanks for your help

JEannot

**Andy Pope** · 05-15-2009, 06:55 AM

charts treat non numeric values, such as "", as zero.

modify your formula for the chart data to

=IF($AE16<30,IF($AE16>0,$L16,NA()),NA())

The chart will not display a data marker for #N/A cells