Page 1 of 2 12 LastLast
Results 1 to 10 of 14

How does Excel recognise a blank cell within IF function...

This is a discussion on How does Excel recognise a blank cell within IF function... within the Excel Questions forums, part of the Question Forums category; Hi All, How can i return a blank cell if the formula is looking at a blank cell with IF ...

  1. #1
    New Member
    Join Date
    Feb 2007
    Location
    Worthing UK
    Posts
    32

    Default How does Excel recognise a blank cell within IF function...

    Hi All,

    How can i return a blank cell if the formula is looking at a blank cell with IF function?

    E.g. IF(A1=" "," "," ")

    (This is part of a bigger statement which returns a date if and when there is a date in the cell, if there isn't it comes up with an answer even though it should be blank)

    Many thanks

  2. #2
    Board Regular Yard's Avatar
    Join Date
    Nov 2008
    Location
    is always easier with the light on
    Posts
    1,857

    Default Re: How does Excel recognise a blank cell within IF function...

    Define "blank" cell?

    Try one of the following:

    =IF(A1="","Blank","Not blank")
    =IF(ISBLANK(A1),"Blank","Not blank")
    "The old girl's chock full to the brim with fizz and ginger" - Bertie Wooster

  3. #3
    Board Regular
    Join Date
    Aug 2005
    Posts
    162

    Default Re: How does Excel recognise a blank cell within IF function...

    Hi

    I think the problem may be that " " represents a Null field rather than a zero-length field, changing the condition to "" might solve the difficulty

    Robert

  4. #4
    New Member
    Join Date
    Feb 2007
    Location
    Worthing UK
    Posts
    32

    Smile Re: How does Excel recognise a blank cell within IF function...

    Thanks both,

    This is the other part of the formula...

    =IF(A1>TODAY(),"Y","N")


    Now i need to add - If A1 is blank i.e. has no data in it, i want A2 to be blank also (A2 contains the formula)

    Hope that helps...


    edit: sorry having trouble getting the formula to show up.... - Got it...
    Last edited by papichulo184; Dec 1st, 2008 at 06:59 AM.

  5. #5
    Board Regular Yard's Avatar
    Join Date
    Nov 2008
    Location
    is always easier with the light on
    Posts
    1,857

    Default Re: How does Excel recognise a blank cell within IF function...

    In A2 then:

    =IF(A1="","",myformula)
    "The old girl's chock full to the brim with fizz and ginger" - Bertie Wooster

  6. #6
    New Member
    Join Date
    Feb 2007
    Location
    Worthing UK
    Posts
    32

    Default Re: How does Excel recognise a blank cell within IF function...

    Not working

    If A1 is blank i want A2 (cell with formula in) to be blank also

    and

    If A1 (date) is greater than A3 (date) then i want it to show "Y" or "N"

    I can understand the 2nd part and have that working but i just cant get an empty cell in A1 to return an empty cell in A2, and dont know how to add another IF into an IF formula!

    I hope that clarifies a little


    PS if have this so far... =IF(A1="","","")(A1>TODAY(),"Y","N")

  7. #7
    Board Regular Yard's Avatar
    Join Date
    Nov 2008
    Location
    is always easier with the light on
    Posts
    1,857

    Default Re: How does Excel recognise a blank cell within IF function...

    This formula in A2:

    =IF(A1="","",IF(A1>A3,"Y","N"))

    If that doesn't work, then tell me what it DOES do.

    What's in A1? A manually entered date?
    "The old girl's chock full to the brim with fizz and ginger" - Bertie Wooster

  8. #8
    New Member
    Join Date
    Feb 2007
    Location
    Worthing UK
    Posts
    32

    Thumbs up Re: How does Excel recognise a blank cell within IF function...

    You got it - works as it should thank you very much indeed

    (And no its not a manually entered date in A1)

  9. #9
    Board Regular
    Join Date
    Aug 2007
    Posts
    104

    Default Re: How does Excel recognise a blank cell within IF function...

    Hi all. This thread was the closest thing I could find to the problem I am having. The solution shown on this page did not work for me.

    I'm creating an encrypting "program" where you enter a word or group of words and a lookup command will retrieve the letter typed and change it to a number.
    My problem is that as the formula stands now, a single word works fine and any cells after the numbered cells (after encryption) remain blank. BUT if there are a group of words, every blank cell in between the encrypted words returns an N/A#. I don't know why those would cause the error when the rest of the line does not.

    Here is what I've got (please note that I did not copy/paste the entire LOOKUP section) and notice the error at G18:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Cryptactic.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    12
    stupid error
    13
                              
    14
    ENCRYPT                         
    15
                              
    16
                              
    17
    stupid error 
     
     
     
     
     
     
     
     
     
     
     
     
     
    18
    788743#37767 
     
     
     
     
     
     
     
     
     
     
     
     
     
    19
                              
    20
                              
    21
                              
    22
                              
    23
                              
    24
                              
    25
    A2                        
    26
    B2                        
    27
    C2                        
    28
    D3                        
    29
    E3                        
    Sheet1 

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    Never spit into the wind.

  10. #10
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,007

    Default Re: How does Excel recognise a blank cell within IF function...

    Hi Steve

    every blank cell in between the encrypted words returns an N/A#.
    If I understand correctly there are no blank cells in between words.

    You are using the MID() function to extract the characters and so betweeen words you have a space.

    Have you included a space in your conversion table? How do you want to deal with spaces?

    P. S.

    Next time it would be better to open a new thread, as this is a new problem. You could include a link to this thread.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com