Line Graph Problem: Remove Line Going To Zero

[Cranford803]

I have a graph with various information in it. One is a line graph that tracks hours used in that department per month. The problem is that when it gets to the last month, and there is not yet data for future months, the line goes down to Zero (leavin this big diagonal line that makes the data look funny). I need this line to stop on the last month there is data for. (I do not manually enter the data, it is a formula that I do not wish to delete.) I know I can manually move the data that the line is pulling to make it stop on the last month, but I have a graph for 36 different departments and that's a pain to have to manually adjust 36 graphs each month. Is there a way to tell the line to stop if there is no data? Thanks!!

 

[cornflakegirl]

A chart won't display anything if the cell shows #NA, so you can use IF so that if the value is 0, the cell displays #NA.

 

[Datsmart]

Maybe this earlier thread will help you:
http://www.mrexcel.com/forum/showthread.php?t=294320

 

[dbig]

I have a similar problem and #N/A (via a formula resulting in function NA()) does not seem to work. My chart still has the bicycle kick stand and 0's out the remaining months.

More specifically ... I have a monthly chart (column with line overlay) that contains 12 months, but only two months of data so far. The columns behave fine, but the line chart wants to plot 0's instead of turning off when it sees the #N/A. The #N/A is via a formula resulting in NA() so it IS #N/A and not "#N/A". The Column's data beyond November are blanks instead of NA(), but they do not display anything in the out months so all's good there. But how can I turn off the chart's lines for the out months? (... in Excel 10)