Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Charting 'Empty' Values--Please Help! (97, SR 1)

    I have searched this forum, and I have found a few posts that dance around my issue. The Microsoft Knowledge Base does as well. My issue is that I would like to plot nothing on my chart when there is no data. Since I use a formula

    =IF(NOT(AB4>0),"",AB3/AB4*100)

    in the cells as my chart's source, Excel interprets this as zero, rather than nothing, and plots it as such. If I change the formula to

    =IF(NOT(AB4>0),NA(),AB3/AB4*100)

    Excel interprets this as the #N/A error, and labels the data point "#N/A." I'm sure there is someone with a great deal of skill and imagination who has come up with a workaround, and I'd appreciate their help. My company frowns on macros, by the way, so I'd appreciate solutions--if any--that don't involve macros.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Charting 'Empty' Values--Please Help! (97, SR 1)

    Hi,
    I can't remember if this works in 97, but if you right-click your data labels, choose Format Data Labels and change the number format to a custom format of 0;-0; then your zero labels should appear to be blank if that helps?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charting 'Empty' Values--Please Help! (97, SR 1)

    Select the chart by clicking in it
    Select Tools / Options...
    Select the Chart tab. It displays the settings for the current chart.
    "Plot empty cells" should be set to "not plotted (leave gaps)".

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charting 'Empty' Values--Please Help! (97, SR 1)

    Thanks, Rory. That got it.

Posting Permissions

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