Results 1 to 13 of 13

Thread: If function

  1. #1
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Angry

    Hi

    Im not sure how to use the IF function in the information i have.
    I have attached the spreadsheet. IF C3 is "NO" and the rest of the cells is NO, it must state rejected but if c3 is NO and the rest is yes, then it must state accepted.

    =IF(C3,"NO","ACCEPTED",IF(C5,C7,C9,C11,C13,"NO","R EJECTED")))

    SEE ATTACHED

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This is not a question about MS Office in general, but specifically about Excel, so I have moved it to the Excel forum.

    You haven't attached a workbook, by the way.

    What do you want to happen if C5:C13 contains a mixture of YES and NO?

  3. #3
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787905' date='07-Aug-2009 12:18']This is not a question about MS Office in general, but specifically about Excel, so I have moved it to the Excel forum.

    You haven't attached a workbook, by the way.

    What do you want to happen if C5:C13 contains a mixture of YES and NO?[/quote]


    I have attached the spreadsheet now, yes its a mixture of yes and no in D313. See attached
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You haven't answered my question yet: What do you want to happen if C5:C13 contains a mixture of YES and NO?

    And what should happen if C3 is YES?

    You will have to provide precise and complete specifications.

  5. #5
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If C3 Is yes, then accepted and if c4:13 is no, then rejected. and if c4:c13 is yes, then accepted.

    It's compalsory for a person to have Yes for c4:13 otherwise the application will be rejected but C3 is not that important, a perosn will be accepted even if C3 is NO

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That's still not very clear, but see if

    =IF(COUNTIF(C5:C13,"YES")=5,"Accepted","Rejected")

    does what you want.

  7. #7
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's what I want, thanx so much!!! it worked perfectly

  8. #8
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    I have spoken to my collegue and she just advised me that E6,E8 and E16 are mandotory meaning that if one of the cells i've mentioned is NO, then the application must be automattically rejected. eg, I might have yes for all of them but if E6 is No, then the application must be rejected.

    Also is it possible that when the application forQuote1 is rejected then one cannot proceed to type in C23.

    See attached
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You now have 6 questions that must be answered instead of 5, so you must change the formula in E17 to

    =IF(COUNTIF(E6:E16,"YES")=6,"Accepted","Rejected")

    and similar for G17.

    Then select cell C23.
    Activate the Data tab of the ribbon
    In the Data Tools group, select Data Validation | Data Validation.
    Select Custom from the Allow dropdown.
    Enter =E17="Accepted" in the formula box.
    Activate the Error Alert tab.
    Enter an appropriate error message, for example "You can't enter a price" (without the quotes).
    Click OK.

  10. #10
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    For E10,E12 and E14, it shouldnt reject when I select No. and if the rest is yes and E10 is No, it should accept the application.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In that case, use

    =IF(AND(E6="Yes",E8="Yes",E16="Yes"),"Accepted","R ejected")

  12. #12
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so so much!!

  13. #13
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    =IF((E6="Yes")*(E8="Yes")*(E16="Yes"),"Accepted"," Rejected")

    Regards
    Bosco

Posting Permissions

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