Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #N/A with Graph Labels (X2K)

    I have set up some bar chart graphs with their Data Labels set to Show Value,

    In the data for the graph I have used an if statement to show the data to use or the na() function if no data available to show.

    The problem that I am having is that the Data Label shows the #N/A as the value. If I change the na() function in the formula to a blank - "" then the Data Label shows a 0.

    How can I persuade the graph to just leave an unfilled gap when there is no data


    Peter

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: #N/A with Graph Labels (X2K)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Peter

    Instead of a blank - "" try a space - " "

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #N/A with Graph Labels (X2K)

    Space gives a 0. <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    Odd thing with the na() is that the lable (showing #N/A )wanders around the graph! it seems to get lost with no column to attach itself to.

  4. #4
    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: #N/A with Graph Labels (X2K)

    Hi Peter,
    You can format the data labels and give them a custom number format like
    0.00;-0.00;
    and they should then display nothing rather than 0 for your missing values (assuming you use "" rather than na() in your formulae).
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #N/A with Graph Labels (X2K)

    Many Thanks, Just what I needed <img src=/S/smile.gif border=0 alt=smile width=15 height=15>


    Peter

Posting Permissions

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