Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Charts Referencing Zero's (1)

    When I have a chart whith formulas as its source data, I have an if statement to determine if the figure is zero. If the figure is zero I set the true value to "". The chart plots this a a zero, I want it the chart to skip this plot, how do I do this?

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Charts Referencing Zero's (1)

    Cindy, replace the "" with #N/A. Your formula should look somthing like:<pre>=IF(B1=0,NA(),B1)</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Charts Referencing Zero's (1)

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    I think that you need to set the value to NA() rather than ""

    Peter

  4. #4
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charts Referencing Zero's (1)

    Sam, this then displays #N/A, I was not clear enough. I want the cell to be empty, as it is part of a report. Is there a way to have a Null ( empty cell)?

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Charts Referencing Zero's (1)

    That would be having your cake and eating it too! Assuming that you don't use "" anywhere else you could record a maco to replace all the NA() with "" and vice-versa. If you have "" elsewhere, then replace NA() with " " (bunch of spaces in the quotes). HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Charts Referencing Zero's (1)

    Could you use conditional formating to hide the font when NA#, White on White?
    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>


    Peter

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Charts Referencing Zero's (1)

    Genius! But I cannot get it to work! <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Can you fix the attached worksheet with conditional formatting!
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Charts Referencing Zero's (1)

    not sure how to do it properly so I used reverse logic <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Formated the cell to a white font and used Conditional formating to turn if black if greater than -1!!

    Of course you would have to pick a number out side of the possible range for you data

    Not sure how to send the sheet back to the forum though

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Charts Referencing Zero's (1)

    That works, but I really hate it! However, don't knock sucess. Thanks, Peter! I have attached the worksheet. To summarize, we used the NA() function, formatted the font color white, and conditionally formatted the font color black when the cell was > -65,000. See attached final product.
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    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: Charts Referencing Zero's (1)

    See attached - you need to use Formula Is =ISNA(C4).
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Charts Referencing Zero's (1)

    Knew that there had to be a proper way of doing it <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Peter

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Charts Referencing Zero's (1)

    Much nicer Rory! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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