+ Reply to Thread
Results 1 to 9 of 9

If And, If Or - Or another way to create formula?

  1. #1
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

    If And, If Or - Or another way to create formula?

    Greetings all,

    im playing with an old if and statement i used long long ago but no im brain dead.

    i need to check Cell "X" for the words "pass", "Fail", "Caution" or "No Test".
    If the word is present then return "1, 2, 3, 4" respectively...
    I can then use this to populate a table which will then be used in a stacked horizontal graph (i hope)

    this is probably dead easy for expert regular excel gurus but its been some time for me and i cant seem to get my head round it right now and time is a factor.


    Thanks in advance

    Dave

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: If And, If Or - Or another way to create formula?

    this would be one way...
    =IF(ISNUMBER(SEARCH("pass",A2)),1,IF(ISNUMBER(SEARCH("fail",A2)),2,IF(ISNUMBER(SEARCH("caution",A2)),3,IF(ISNUMBER(SEARCH("no test",A2)),4,""))))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If And, If Or - Or another way to create formula?

    One way:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    12-10-2018
    Location
    Canada
    MS-Off Ver
    Microsoft 365
    Posts
    54

    Re: If And, If Or - Or another way to create formula?

    Here's an IF statement

    =IFS(CELL="pass",1,CELL="fail",2,CELL="caution",3,CELL="no test",4)

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If And, If Or - Or another way to create formula?

    Note: my solution assumes that only those words will be present in the cell whereas Sambo Kid's solution will check for the presence in a longer text string. Horses for courses.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If And, If Or - Or another way to create formula?

    @Cleetus09: IFS is not available in Excel 2010, AFAIK

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: If And, If Or - Or another way to create formula?

    yes, as Trevor states, mine is assuming that the words are likely mixed in with other text, though it will work if the words are alone in the cell too, as his will.

  8. #8
    Registered User
    Join Date
    12-10-2018
    Location
    Canada
    MS-Off Ver
    Microsoft 365
    Posts
    54

    Re: If And, If Or - Or another way to create formula?

    Quote Originally Posted by TMS View Post
    @Cleetus09: IFS is not available in Excel 2010, AFAIK
    bah, didn't realize til afterwards... and to think i was feeling good thinking "i got this one..." *sigh*

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: If And, If Or - Or another way to create formula?

    And really, you need something like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to cater for the not present scenario

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to create list of items selected for reports (array formula?)
    By Cidona in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-06-2018, 02:09 AM
  2. Replies: 3
    Last Post: 07-05-2017, 04:48 PM
  3. [SOLVED] VBA to create a relative formula in a conditional format (rather than an absolute formula)
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2016, 08:00 AM
  4. Replies: 4
    Last Post: 07-16-2013, 10:32 AM
  5. [SOLVED] I want to create a formula in one sheet, that will function as a formula in other sheets
    By johnw993 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2013, 07:07 PM
  6. Replies: 7
    Last Post: 08-22-2005, 08:05 AM
  7. Replies: 2
    Last Post: 07-01-2005, 02:05 PM

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