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

 

[notsurewhattodo]

I have yet to find a solution to making zero or null values not show up in excel line charts, 2007 edition and beyond. At this point I think the only solution is to use VBA to parse the data and remove all formulas from cells that contain a zero or null value... Not ideal but it works.

 

[Steve=True]

Hi Notsurewhat to do:

You can use the NA() function to hide the Zero data points in a line and other charts.

I would suggest that you make an alternate charting range that looks at your data that you want to graph.

If the number=0 then put in the NA() function in the new chart range and if >0, then put the number in the new chart range. For example: =if(b2=0,NA(),b2). This will not plot the

Here is a more detailed explaination of this technique

http://www.exceldashboardtemplates.com/?p=696

Steve=True

 

[notsurewhattodo]

I've tried using the NA() function but this still doesn't work for continuous line graphs in Excel '08. The data will show a #NA value, but Excel still plots data between points. The NA() value does work for pie graphs and stacked bars.