Line Charts showing zero

[Graeme155]

the future months obvioucly have zero sales at present, but the chart graphs these. Is there a way for the line graph to stop when the number drop to zero?

 

[Steve=True]

Yes, set your formula for your graph points to =NA()

like if(x=0,na(),x)

steve=true
www.exceldashboardtemplate.com

 

[Benjamin1986]

What about when there is a gap in data?


I have two lines that are close but not identical (two instruments measuring the same stream), and I need to show which is being used. I tried one line in blue and the other in purple, but since this is a column with a formula, a "" goes to zero, and an NA() interpolates if there is data on both sides. I cannot just get line 1 to not draw between the 2 and 5 or line to to not connect the 6.98 and 6.17

Line 1 Line 2
1 -
2 -
- 3.21
- 3.98
5 -
- 6.17
7 -
8 -

I can't seem to find any way to graph a gap with a formula.

 

[Steve=True]

Benjamin 1986

This can be done but it requires you to draw lines over the lines between the points that should not be connected.

Andy Pope has some formulas in the download on this topic.

http://www.andypope.info/charts/brokenlines.htm


Steve=True
www.exceldashboardtemplates.com

 

[Benjamin1986]

Thanks for the suggestion, but that doesn't work if the lines overlap with a different chart. The overlay will cross the line and blank out that one too. This is going before a lot of non-technical people showing all manner of data automatically (so no manual tweaks are possible), and charts doing inexplicable things is not on their list of favorite things.

 

[Steve=True]

Can you use VBA and/or Macros?

If the data is deleted, it does not interpolate.

Perhaps you can have a VBA script or macro delete the data points.

 

[Steve=True]

Only other options would be to plot them using an XY chart:

Here are 2 different tutorials on how to change the colors of the lines using XY charts

http://www.andypope.info/charts/conditionalline2.htm

http://www.andypope.info/charts/conditionalline.htm

Steve=True
www.exceldashboardtemplates.com