+ Reply to Thread
Results 1 to 4 of 4

If statement meant to return blank cells doesn't return blank cells?

  1. #1
    Registered User
    Join Date
    03-02-2007
    Posts
    10

    Lightbulb If statement meant to return blank cells doesn't return blank cells?

    I call upon the collective genius of this forum once again with a "simple" question.

    Why does the following statement not really return a blank cell?

    =IF('Rapportage Sheet; Januari-Juni'!IJ4=0;"";'Rapportage Sheet; Januari-Juni'!IJ4)

    Say I paste this formula in cell A1, and the referenced cell is empty, there appears to be no data in A1 (yay!). However if I check with =ISBLANK(A1) it returns "False". The reason this bugs me is because I have charts that are supposed to skip blank cells (the option "Plot empty cells as: Not plotted (leave gaps)" is enabled) but instead of leaving gaps it plots these cells as "0".

    Is there a way to make a cell blank properly?

    P.S.

    =IF('Rapportage Sheet; Januari-Juni'!IJ4="";"";'Rapportage Sheet; Januari-Juni'!IJ4) returns the same results.

  2. #2
    Valued Forum Contributor
    Join Date
    09-23-2005
    Location
    Bristol, UK
    MS-Off Ver
    2007
    Posts
    664
    Hi,

    Try =countblank(a1) and see what you get. I've just tried a similarly styled IF formula and it counts "" as blank.

    Also - looks like you're using semi-colons instead of commas in your formula - don't know if this is having an adverse effect.



    HTH,

    SamuelT
    Last edited by SamuelT; 11-21-2007 at 05:02 AM.

  3. #3
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    Hi,

    From what you are saying, you are testing a cell A1 that has a formula in it for ISBLANK - so it will come up false as the formula will is there. The result of the formula is blank.

    The graph should still work ok.

    Ed

  4. #4
    Registered User
    Join Date
    03-02-2007
    Posts
    10
    Thx for the replies so far. Unfortunately the charts don't work ok. I've attached an example with a relevant section of the workbook so it's easy to see. The problem clearly shows in the tab "Rapportage Sheet; Januari-Juni".
    Attached Files Attached Files

+ 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