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

[vchawl1]

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?

 

[Airfix9]

Try using NA(), which is unplottable and is the value that I use to cut a line-graph off without plotting zeroes.

Example: IF(A1>B1,NA(),A1/B1) - If A1 is bigger than B1 then the response would be #N/A which is, as I mentioned, unplottable.

 

[vchawl1]

This is a good option but my excel file is a dashboard so I have to show the data and the graph and am trying to make it easier for the end user to just enter data and other calculations will take place.

I tried to modify my IF statement and the graph worked but the number in the subsequent months when there was no data showed up as #N/A which I want to avoid.

My current if statement:=IF(B40=0,"",B40/B32) any sugesstions to modify this to display the data and make the graph work?

Thanks.

 

[R-D]

You will have to change your "" to NA() as suggested.

if you need to "hide" the #N/A your eceive when doing this, set a conditional format (using ISNA() )to color the foreground and background colors the same.

 

[vchawl1]

Can you please walk me throught the conditional formatting in detail or can I use some other function along with the NA() in the if statement to avoid displaying the #n/a like the ISEROR function maybe?

 

[R-D]

You NEED to return NA() in order for the graph to not draw this mark. If you hide it using any error checking, you will again be drawing the 0 on your graph.

To use the conditional formatting, highlight your range where the error will occur. On the Home ribbon in the Styles section click the Conditional Formatting button and select New Rule. Select "Use A Formula" (last item) and enter =ISNA(A1) where A1 is the first cell you selected in your range. Excel will automatically adjust this as necessary for the entire selection. Then just set your foreground and background colors to the same (ie. white on white).