Hi all,

I am trying to chart a series of data points from a table which looks up
values dependant on the contents of a seperate cell with a drop down list.
The table / chart is designed to be used until the end of the year, so
obviously values after today show as blank, (there are formulas already in
the table which will allow the graph to populate over time).

Is there a way of not charting cells with a blank value in Excel 2007 to
prevent the graph displaying these data points as zero?

Thanks in advance

Hi,

You need to output #N/A , using the NA() formula, instead of zero or text.

so your formula would be constructed like

=IF( <test for value , <Value , NA() )

The use of NA will only suppress data markers it will not break the line.
Only truly empty cells will cause line breaks

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"pete24919" wrote in message
...
Hi all,

I am trying to chart a series of data points from a table which looks up
values dependant on the contents of a seperate cell with a drop down list.
The table / chart is designed to be used until the end of the year, so
obviously values after today show as blank, (there are formulas already in
the table which will allow the graph to populate over time).

Is there a way of not charting cells with a blank value in Excel 2007 to
prevent the graph displaying these data points as zero?

Thanks in advance

Thanks Andy - this has solved the problem

Pete

"Andy Pope" wrote:

Hi,

You need to output #N/A , using the NA() formula, instead of zero or text.

so your formula would be constructed like

=IF( <test for value , <Value , NA() )

The use of NA will only suppress data markers it will not break the line.
Only truly empty cells will cause line breaks

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"pete24919" wrote in message
...
Hi all,

I am trying to chart a series of data points from a table which looks up
values dependant on the contents of a seperate cell with a drop down list.
The table / chart is designed to be used until the end of the year, so
obviously values after today show as blank, (there are formulas already in
the table which will allow the graph to populate over time).

Is there a way of not charting cells with a blank value in Excel 2007 to
prevent the graph displaying these data points as zero?

Thanks in advance