Line graphs with null values

[Darren Hagarty]

I have a line graph which is plotting values from a worksheet for the
period January through December. I would like the chart's X-Axis to
show each month from January to December, but if there is no value yet
for some of the later months, I would like it plot nothing at all.

In other words, if there is data only for January to October, I would
like the plot line to cease in October and leave the area in the chart
dealing with November and December empty until there is data. Does
anyone have any thoughts as to how this might be accomplished?

Kind regards,
Darren

 

[Andy Pope]

Hi Darren,

If the cells for Nov/Dec are empty then the line should stop at October.
If the cells contain a formula which returns zero for Nov/Dec then
modify the formula to use NA() function instead of zero.

You might also check that the option for Chart - Plot Empty cells as:
is set to Not plotted(leave gaps). Select the chart and use the menu
Tools > Options. Click Chart tab.

Cheers
Andy

 

[Guest]

Hi Andy; I too have the same promble as Darren, but when I use the NA() the
line stops but the #NA() shows as a label in place of a 0. How can I prevent
this?

Leroy

 

[Andy Pope]

The simplest way is to use a help column to create the text to be
displayed in the data label. For this you can use "" in the formula.
Then link the data labels to the cells. Use Rob Bovey's free addin for this.
http://www.appspro.com/Utilities/ChartLabeler.htm

Cheers
Andy