+ Reply to Thread
Results 1 to 8 of 8

charting the data with blanks

  1. #1
    Registered User
    Join Date
    08-15-2012
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    16

    charting the data with blanks

    when i chart a series of data in which some of the data are from a equation of =If(a=b, 123, ""). Although the cell shows a "" but in chart the line will drop to zero. I want it to be a missing data and the line could interpolate this point. How could i do that? thank you very much.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: charting the data with blanks

    =If(a=b, 123, NA())
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    08-15-2012
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: charting the data with blanks

    thanks for your reply, i know i could put an error message on the cell, then the chart will ignore it, but i prefer to show a blank cell to a error message in the data file. Is there any other method to chart the blank cell? thank you very much.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: charting the data with blanks

    use conditional formatting to hide the #N/A

  5. #5
    Registered User
    Join Date
    08-15-2012
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: charting the data with blanks

    In conditional formatting, i add a new rule on "use a formula to determine which cell to format" and add "=NA()" to the rule description and change the format colour to white (so that the cell figure cannot be seen). however, nothing happened. did i do something wrong? thanks again

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: charting the data with blanks

    I guess so, if it's not working. What exactly is impossible to say.

    I just used the Cell value is Error to change font colour. Currently orange so you can see it, you can change it to white.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-15-2012
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: charting the data with blanks

    Mr. Pope, thanks a lot.

  8. #8
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: charting the data with blanks

    @ ceciwong

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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