Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Please help me with simple IF worksheet function.

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a worksheet function that looks like this IF(A1=1,"open","closed")
    THe value of A1 will on ly be three things (0,1,???). So I'd like to make the formula to do this:
    When A1 is 0, I want it to say "Closed"
    When A1 is 1, I want it to say "Open"
    When A1 is ???, I want it to just be blank (or it can also have "???", it just can't say Open or Closed.
    THanks for any help.
    de

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-01 04:43, de8212 wrote:
    I have a worksheet function that looks like this IF(A1=1,"open","closed")
    THe value of A1 will on ly be three things (0,1,???). So I'd like to make the formula to do this:
    When A1 is 0, I want it to say "Closed"
    When A1 is 1, I want it to say "Open"
    When A1 is ???, I want it to just be blank (or it can also have "???", it just can't say Open or Closed.
    THanks for any help.
    de
    =IF(LEN(A1),IF(A1="???","???",IF(A1=1,"Open","Closed")),"")


  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Toronto
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try: =if(a1=1,"open",if(a1=0,"closed",""))
    I presume by ??? you mean anything other than 1 or 0.
    Aladin: Why is your formula so complicated, does it have some advantages I can't see?
    On 2002-05-01 04:43, de8212 wrote:
    I have a worksheet function that looks like this IF(A1=1,"open","closed")
    THe value of A1 will on ly be three things (0,1,???). So I'd like to make the formula to do this:
    When A1 is 0, I want it to say "Closed"
    When A1 is 1, I want it to say "Open"
    When A1 is ???, I want it to just be blank (or it can also have "???", it just can't say Open or Closed.
    THanks for any help.
    de

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-01 04:57, Wayne Duncan wrote:
    Try: =if(a1=1,"open",if(a1=0,"closed",""))
    I presume by ??? you mean anything other than 1 or 0.
    Aladin: Why is your formula so complicated, does it have some advantages I can't see?
    Wayne,

    I know it looks a bit ludic. Amendable though if it covers too much.

    You had to make a caveat: I presume by ??? you mean anything other than 1 or 0.

    I took "???" as a possible value of A1.

    Try your version when A1 is truly blank. Hard to tell whether its result is intended.

    Aladin

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the responses. I will try and see if I can get it to work.


    Aladin
    The data is automatically generated into the spreadsheet and it will only be a zero, a one, or ???. So ??? is definately the actual entry into the spreadsheet.
    Thanks alot.

    de



  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Worked great. Thank you for the help.

    de

  7. #7
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When Aladin writes a formula ,
    he looks very far ,and he is absolutely correct !!!




Some videos you may like

User Tag List

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
  •