+ Reply to Thread
Results 1 to 4 of 4

Line graphs with null values

  1. #1
    Darren Hagarty
    Guest

    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. #2
    Andy Pope
    Guest

    Re: Line graphs with null values

    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. #3
    Leroy-P
    Guest

    Re: Line graphs with null values

    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. #4
    Andy Pope
    Guest

    Re: Line graphs with null values

    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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1