Hide Cells with Zero Value in Excel 2007 while using line chart ty

[Chicago]

Hello All,
I am using Excel 2007 and have a situation with the charting functionality.
For example - I am trying to set up automatic update chart update for Jan 08
– Dec 08 data i.e. user just enters data each month and the graph trend shows
up each month. I have done this a lot of times but seem to have a unique
situation with excel 07 this time when I am using the line chart type to show
the trend.
The date cells that have some kind of formulas used i.e. if function or
Iserror function etc. I tried to test the chart by entering data for January
it works fine with bar graph only displaying Jan 08 data and other months
zero but when I switch the chart type to line chart instead of just showing a
point for January 08 data it shows the Jan 08 data and other months as zero
i.e. graph line drops from the value to zero.
I tried to set the hidden empty cells as zero and gaps (Excel 07 option) but
it did not work. Next I removed the formulas i.e. the IF function and other
functions and manually entered the data……line chart worked fine i.e. did not
display the subsequent month’s data i.e. Feb 08 – Dec 08 which is all zero.
This to me is weird that once you remove the formulas the line chart works
fine by not displaying the zero cells in the chart but the bar chart works
even though it has formula.
Can someone please help me with my situation as I would like to automate the
chart using the line graph and at the same time keep the formulas in the
cells?

 

[Jon Peltier]

Your formula is probably something like

=IF(something,value,"")

The "" looks blank, but it's a piece of text, and Excel assigns a value of
zero to text. In line and XY charts, if you change "" to NA(), the formula
returns #N/A, which is not plotted with a marker. You can hide the error
with conditional formatting.

- Jon