I'm working with an excel file that has a line chart in a different sheet of
the workbook. The data being used for the charts has an If statement that
returns nothing if the results of two cells are less than 0. As the
spreadsheet gets updated the formula the results in the cells change from 0
to actual values.

What I'm looking to do is to get the Line Chart not to Plot the empty cells.
I have tried copying just the values from the formula but it does not work.
I hae tried changing the options in the Chart tab in the Options menu, but it
does not work. The only way I can get it to stop Plotting the cells with 0
in it is by deleting everything from those cells.

Is there a workaround for this?

Thanks,

The IF statement doesn't return nothing, it returns "", which is text, and
numerically equivalent to zero. Change "" in the formula to NA(). This
leaves an ugly #N/A error in the cell (hide it with conditional formatting
if you need to), but the point isn't plotted in the chart. Any line
connecting the points will be interpolated between the points on either side
of the missing point.

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


"Marilyn" wrote in message
...
I'm working with an excel file that has a line chart in a different sheet
of
the workbook. The data being used for the charts has an If statement that
returns nothing if the results of two cells are less than 0. As the
spreadsheet gets updated the formula the results in the cells change from
0
to actual values.

What I'm looking to do is to get the Line Chart not to Plot the empty
cells.
I have tried copying just the values from the formula but it does not
work.
I hae tried changing the options in the Chart tab in the Options menu, but
it
does not work. The only way I can get it to stop Plotting the cells with
0
in it is by deleting everything from those cells.

Is there a workaround for this?

Thanks,

Jon,

Sometimes I get so caught up with the problem that I forget to search the
threads for the soultion :) After posting the question I found your answer
in one of the threads and I have to say that it works great!!!

Thanks for the quick response and Happy New Year!!!!

"Jon Peltier" wrote:

The IF statement doesn't return nothing, it returns "", which is text, and
numerically equivalent to zero. Change "" in the formula to NA(). This
leaves an ugly #N/A error in the cell (hide it with conditional formatting
if you need to), but the point isn't plotted in the chart. Any line
connecting the points will be interpolated between the points on either side
of the missing point.

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


"Marilyn" wrote in message
...
I'm working with an excel file that has a line chart in a different sheet
of
the workbook. The data being used for the charts has an If statement that
returns nothing if the results of two cells are less than 0. As the
spreadsheet gets updated the formula the results in the cells change from
0
to actual values.

What I'm looking to do is to get the Line Chart not to Plot the empty
cells.
I have tried copying just the values from the formula but it does not
work.
I hae tried changing the options in the Chart tab in the Options menu, but
it
does not work. The only way I can get it to stop Plotting the cells with
0
in it is by deleting everything from those cells.

Is there a workaround for this?

Thanks,