Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: 0 (zero) values shown in line graph for empty values - (EDIT

  1. #1
    Guest

    Default

    I use a bar graph to display actual data and a line graph to display Budget data. Now, for obvious reasons, I only want to show data until the current month. The problem is that the line graph shows 0 (zero) values (which are the result of a formula like IF[ (A1="FEB-02";B1;"") ]and the bar graph does not show 0 (zero) values.
    I get the following situation in a graph:
    ------ Jan Feb Mar Apr May
    Actual 5 6 8
    Budget 6 5 5 0 0
    Why does the line not show the same behaviour as the bar ? I do not want the line to show 0 values or "" as 0 (zero).
    BTW. Options-Tools-Graph shows: "Plot empty cells as: Not Plotted (leave gaps)"'
    Apparently this only works for the Bar-Graph.
    What can I do or is this just a peculiar habit of Excel2000 ?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    246
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think this is one of those delightful "features" of Excel.

    The solution I use is to delete any records where the value is zero. If your chart is using formulas, then you can avoid the problem for "future months" by only copying down your formula for the months where you have data.

    It isn't an issue on a bar or column chart as a zero value would display a bar/column of zero height - i.e. not visible. When using the line chart, the data point markers are visible as is the line from the previous point which did contain a value.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  3. #3
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Change your IF formula to return NA() instead of "". Excel won't graph NA() values.

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  4. #4
    Guest

    Default

    I have tried using blank, N/A(), #N/A, 1/0(to give #ERR) and all to no avail. I still get the graph line plunging to zero for the rest of the year.

  5. #5
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-19 07:50, Anonymous wrote:
    I have tried using blank, N/A(), #N/A, 1/0(to give #ERR) and all to no avail. I still get the graph line plunging to zero for the rest of the year.
    No, not N/A(), NA(). NA() returns the #N/A value for Excel. Try this:
    IF(A1="FEB-02",B1,NA())


    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  6. #6
    Guest

    Default

    Haha, the Anonymous is another anonymous as me Anonymous ! Apparently an anonymous with the same problem.
    You know what ? I am going to register myself.
    From now on I will be Dr. Strangelove !!!

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Tilburg, the Netherlands
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yep, now I'm Dr. Strangelove. (Go watch that movie).
    All people who took the time&effort to react upon my request. Thanks !
    The NA() definitively did the trick !!
    Life is Great, Excel isn't bad - just peculiar sometimes...

  8. #8
    New Member
    Join Date
    May 2013
    Location
    Silicon Valley, California
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Barrie! I had been pulling my hair out on this exact issue and found your post from 11 years ago -- still works like a charm today in current versions of Excel and makes my line charts look much better.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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