Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Odd Excel quirk?

    I have OfficeXP.

    While helping a coworker develop a formula, I noticed that COUNTBLANK and ISBLANK treat "" in different manners.

    COUNTBLANK will count "" as blank, while ISBLANK("")=False

    Seems kind of inconsistent to me.
    Inspiration Through Fermentation

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Actually not inconsistent, because one COUNTBLANK is an integer value and "" in the cell equates to 0. The other ISBLANK is Boolean looking at the contents of the cell, and in this case you have two characters " and " in the cell.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by shades
    Actually not inconsistent, because one COUNTBLANK is an integer value and "" in the cell equates to 0. The other ISBLANK is Boolean looking at the contents of the cell, and in this case you have two characters " and " in the cell.
    But if "" = 0 then I still see it as ISBLANK(0)=FALSE

    If I put in the following:

    A1 = BLANK
    A2 = ""
    A3 = 0
    A4 = COUNTBLANK(A1:A3) = 2
    B1 = IF(ISBLANK(A1),1,0) = 1
    B2 = IF(ISBLANK(A2),1,0) = 0
    B3 = IF(ISBLANK(A3),1,0) = 0
    B4 = SUM(B1:B3) = 1

    I just would expect COUNTBLANK(A1:A3) to be the same as
    (PSUEDO) - SUM(IF(ISBLANK(Ax),1,0)
    Inspiration Through Fermentation

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Using your setup, the result of this formula in XL is 1, not 2

    =COUNTBLANK(A1:A3)

    And really for the ISBLANK formulas ( =ISBLANK(A1) ) the result is

    TRUE
    FALSE
    FALSE

    (since they are Boolean formulas)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, for your second cell (A2), you are putting in

    =""

    not

    ""

    Therefore, it does evaluate to 2, which confirms my first post.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I understand (and take) your point. It's just that, even though there are 2 functions in Excel containing "BLANK", blank means different things to each formula.

    BTW:.... "old, slow... but at least I'm inconsistent."

    Better to be old and inconsistent than old and incontinent!
    Inspiration Through Fermentation

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by RedNeckGeek
    I understand (and take) your point. It's just that, even though there are 2 functions in Excel containing "BLANK", blank means different things to each formula.
    Actually it means the same thing to both, but the result of each formula is different...

    BTW:.... "old, slow... but at least I'm inconsistent."

    Better to be old and inconsistent than old and incontinent!
    So true. On one msg bd I didn't spell check before putting up my signature, and I spelled it that way. Needless to say I caught it and changed it, so it didn't stay long - someone would have had to have been quick on the draw and visited my post in a very short time span. But.... I almost became incontinent thinking about it staying that way!
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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