Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Darren Hagarty
 
Posts: n/a
Default Line graphs with null values

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
  #2   Report Post  
Andy Pope
 
Posts: n/a
Default

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

Darren Hagarty wrote:

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, Microsoft MVP - Excel
http://www.andypope.info
  #3   Report Post  
Leroy-P
 
Posts: n/a
Default

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" wrote:

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

Darren Hagarty wrote:

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, Microsoft MVP - Excel
http://www.andypope.info

  #4   Report Post  
Andy Pope
 
Posts: n/a
Default

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

Leroy-P wrote:
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" wrote:


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

Darren Hagarty wrote:


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, Microsoft MVP - Excel
http://www.andypope.info


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Zero values in a log chart Joelle_Smith Charts and Charting in Excel 2 May 8th 23 03:42 AM
linked values in a chart Bill H. Charts and Charting in Excel 2 January 2nd 05 05:29 AM
How to force continuous lines in line charts wal50 Charts and Charting in Excel 1 December 21st 04 10:43 PM
create space in line chart between points, linked to pivot table Mike -Z- Charts and Charting in Excel 1 December 7th 04 09:39 PM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 03:56 AM


All times are GMT +1. The time now is 07:43 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"