Results 1 to 10 of 10

Thread: If statement

  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    24
    Thanks
    19
    Thanked 0 Times in 0 Posts

    If statement

    Im trying to write an if statement that says if cell A1 says "17,30,87,38" then change cell B1 to say "Home" if not say "". It never says "Home" only ""
    =If(A1="17,30,87,38","Home","")

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Herman,

    Welcome to the Lounge as a new poster.

    I think you are saying that if A1 = 17 or 30 or 87 or 38 then the value in B1 should be Home otherwise it is blank?

    Cell B1 Formula: =If(A1=17,"Home",If(A1=30,"Home",If(A1=38,"Home",I f(A1=87,"Home",""))))

    Another Option: =If(OR(A1=17,A1=30,A1=38,A1=87),"Home","")

    HTH
    Last edited by RetiredGeek; 2014-10-26 at 22:55.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    And another option:
    =If(OR(A1={17,30,38,87}),"Home","")
    Regards,
    Rory

    Microsoft MVP - Excel

  4. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2014-10-27)

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Cool! I didn't know you could do that!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Cool? It's very cool!

    I'm always writing OR statements where I repeat the cell within the parens. Using the {set} braces (or array braces) is very cool.

    Fred

  7. #6
    New Lounger
    Join Date
    Oct 2014
    Posts
    24
    Thanks
    19
    Thanked 0 Times in 0 Posts
    All the number are in one cell with command in between them.

  8. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    If the (text) string: 17,30,87,38 is in cell A1, and you place your formula in B1: =If(A1="17,30,87,38","Home","")

    That should work. Be sure you don't have an extra space after the 38, or any other extra characters.
    Make sure all of the separators are commas and there are no extra spaces.

  9. The Following User Says Thank You to kweaver For This Useful Post:

    Herman Escobedo (2014-10-27)

  10. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Herman,

    Could you possibly post a sample worksheet?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #9
    New Lounger
    Join Date
    Oct 2014
    Posts
    24
    Thanks
    19
    Thanked 0 Times in 0 Posts
    I got it working thank you all. I was missing the "" around home.
    Last edited by Herman Escobedo; 2014-10-28 at 10:37.

  12. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG

    Further to Roy's post #3, you can also use
    {=IF(OR(A1=myList),"Home","")}

    entered as an array formula, where you have defined name myList referring to a block of cells containing the required values. (It doesn't matter if the named range is a vertical list of cells or a horizontal range of cells).
    This can be useful if the list of valid values is long or you want to change them easily.

    zeddy

Tags for this Thread

Posting Permissions

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