+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : Charting missing data: empty cells bug

  1. #1
    Registered User
    Join Date
    10-26-2009
    Location
    Nice
    MS-Off Ver
    Excel 2007
    Posts
    5

    Charting missing data: empty cells bug

    Hi

    I'm trying to chart data with some missing points. I read posts on this forum on this topic and checked with my settings but they seem to be OK. Still, my empty cells are plotted as zeros. Only one (!) cell out of 5 is not plotted(and I failed to figure out how that one was different from the rest).

    Here are the current settings:

    - Excel version is 2007 French.
    - Cells are not hidden, but protected.
    - They are customized at "empty cell" format (;;; ) (a tip I read on internet, but that might be wrong)
    - Empty cells are set to be plotted as gaps.

    Your help would be very much appreciated.
    Last edited by kazim; 10-26-2009 at 07:36 PM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669

    Re: Charting missing data: empty cells bug

    The formatting doesn't change the underlying cell content, which is what gets plotted. It certainly does not make it empty.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-26-2009
    Location
    Nice
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Charting missing data: empty cells bug

    Thanks shg.
    What I had initially in those cells were two dots. Now, I deleted them and personalized the cell format to ;;; (I read that this action means that excel starts actually treating it as an empty cell). Do I have to change smth in the settings then?

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669

    Re: Charting missing data: empty cells bug

    In A1 enter 1 and format as ;;;

    In B1, enter =A1 and format as General.

    Does Excel think A1 is empty?

  5. #5
    Registered User
    Join Date
    10-26-2009
    Location
    Nice
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Charting missing data: empty cells bug

    Quote Originally Posted by shg View Post
    In A1 enter 1 and format as ;;;

    In B1, enter =A1 and format as General.

    Does Excel think A1 is empty?
    Oops. No, it doesn't. So, it's just good to hide the cell content.
    But my cells are empty, though. Why does it treat them as zeros? Is there something I missed?

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669

    Re: Charting missing data: empty cells bug

    But my cells are empty, though. Why does it treat them as zeros?
    What cells are empty? Excel offers options of plotting as zero, not plotting (leaving a gap), or interpolating between the adjacent points.

  7. #7
    Registered User
    Join Date
    10-26-2009
    Location
    Nice
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Charting missing data: empty cells bug

    Sorry if I was not clear enough. I actually put in my first post.
    - Empty cells are set to be plotted as gaps.
    So I did check those settings, and the current option is the "gap" option. Still, the empty cells are plotted as zeros. That's where I'm stuck.

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669

    Re: Charting missing data: empty cells bug

    I think the cells are not really empty, as in contain NOTHING.

  9. #9
    Registered User
    Join Date
    10-26-2009
    Location
    Nice
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Charting missing data: empty cells bug

    Quote Originally Posted by shg View Post
    I think the cells are not really empty, as in contain NOTHING.
    Wow! I feel really stupid.. You were right: they actually contained one tiny little space.
    That's the power of discussing things. You really helped me out.Thanks a lot, shg!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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