+ Reply to Thread
Results 1 to 7 of 7

IF "Pass","Fail"

  1. #1
    Registered User
    Join Date
    11-14-2010
    Location
    HK
    MS-Off Ver
    Excel 2007
    Posts
    28

    IF "Pass","Fail"

    How do I set the formula if

    if F1...I1 <10 = Pass, >=10 Fail (and highlighted red), and any cell between F1....I1 is empty = blank
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,733

    Re: IF "Pass","Fail"

    Hi, if you have numbers in E1...I1 you could try in K1 (Reading your example)

    =IF(E1="","",IF(E1<10,"Pass","Fail"))

    To highlight red, in K1 in format/conditional formatting select "Cell value is" "equal to" and write in the last window

    ="Fail".

    Formula in K1 and its condional fomat to be copied to L1:O1


    Hope it helps
    Last edited by canapone; 11-22-2010 at 11:38 PM.

  3. #3
    Registered User
    Join Date
    11-14-2010
    Location
    HK
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: IF "Pass","Fail"

    But the problem is "<10" is not a number nor value
    if it is equal to 9,8,7,6.....it would be easier to sort out, however it is "<10"


    Quote Originally Posted by CANAPONE View Post
    Hi, if you have numbers in E1...I1 you could try in K1 (Reading your example)

    =IF(E1="","",IF(E1<10,"Pass","Fail"))

    To highlight red, in K1 in format/conditional formatting select "Cell value is" "equal to" and write in the last window

    ="Fail".

    Formula in K1 and its condional fomat to be copied to L1:O1


    Hope it helps

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,733

    Re: IF "Pass","Fail"

    Hi, maybe: just an attempt

    =IF(F1="","",IF(F1="<10","Pass","Fail"))

    In F1 if I've understood you can find a string like "<10": it means Pass, In other case "Fail", if the cell is not empty.

    Regards
    Last edited by canapone; 11-22-2010 at 11:54 PM.

  5. #5
    Registered User
    Join Date
    11-14-2010
    Location
    HK
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: IF "Pass","Fail"

    can I set it to? :
    =IF(F1="","",IF(F1="<10" or <100000,"Pass","Fail"))

    or

    tell the formula that if it is not a number (no matter "<1" "<10" "<10000") = Pass ??

    because less than 1000000 or "<1" = Pass



    Quote Originally Posted by CANAPONE View Post
    Hi, maybe: just an attempt

    =IF(F1="","",IF(F1="<10","Pass","Fail"))

    In F1 if I've understood you can find a string like "<10": it means Pass, In other case "Fail", if the cell is not empty.

    Regards
    Last edited by cicihk; 11-23-2010 at 12:07 AM.

  6. #6
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,733

    Re: IF "Pass","Fail"

    Hi,

    =IF(F1="","",IF(ISNUMBER(F1),"Fail","Pass"))

    or

    =IF(F1="","",IF(ISERROR(FIND("<",F1)),"Fail","Pass"))

    You can also use AND OR to adjust the conditions.

    For istance in the there is a "<" in the string but the number is not OK (sorry my English: it's 5 am here) we can adjust the formula excluding some values.

    Regards

  7. #7
    Registered User
    Join Date
    11-14-2010
    Location
    HK
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: IF "Pass","Fail"

    Canapone, You're superb!! Have a good sleep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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