+ Reply to Thread
Results 1 to 7 of 7

Alternative to using multiple nested IF statements

  1. #1
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,176

    Alternative to using multiple nested IF statements

    I have a spreadsheet set up as follows:

    Col A........Col B......Col C........Col D.....Col E.........Col F......Col G........Col H
    Student....Grade....Student.....Grade....Student.....Grade....Student.....Grade....
    Mike..........Pass......Tim..........Pass.......Joe............Fail.........Sue.........Pass
    Mike..........Pass......Tim..........Pass
    Mike..........Pass......Tim..........Fail
    Mike..........Pass......Tim..........Pass.......Joe............Pass........Sue.........Pass

    For each line in file, I need to determine if all students have passed the course. If all students have passed then I need to make a notation in another cell which indicates "OK". If one or more students have failed, then I need to make a notation of "Review". For example:

    Line 1 = Review
    Line 2 = Ok
    Line 3 = Review
    Line 4 = Ok

    I am trying to avoid having to write some lengthy nested if statement to solve. In some cases, I may have as many as 20 students in one line and just not sure how to test if all have passed or at least one has failed. Thanks in advance for any comments.
    Last edited by maacmaac; 09-15-2011 at 11:21 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Alternative to using multiple nested IF statements

    How about?

    =IF(COUNTIF(A2:H2,"Fail")>0,"Review","Ok")

    asuming first line is in A2:H2
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,176

    Re: Alternative to using multiple nested IF statements

    That works great. Just one more question. In other cases, there may be more than one possibility of key words for the grade. For example say the key words are "Fail" and "Pending". The formula also works for this scenario.

    =IF(OR(COUNTIF(A2:H2,"Fail"),COUNTIF(A2:H2,"Pending"))>0,"Review","Ok")

    My question is what if I had 15 words that I needed to key in on? Instead of having 15 different COUNTIF sections in the formula, is there a more effecient way to write the formula that what I have presented (i.e. could I create a list of key words and reference the list in the formula).

    Thanks again.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Alternative to using multiple nested IF statements

    So if all have to "Pass" to get an "Ok", then maybe this formula?

    =IF(COUNTIF(A2:H2,"Pass")=COUNTA(A2:H2)/2,"Ok","Review")

  5. #5
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,176

    Re: Alternative to using multiple nested IF statements

    Much easier than the other way. I had to tweak your formula from:

    =IF(COUNTIF(A2:H2,"Pass")=COUNTA(A2:H2)/2,"Ok","Review")

    to:

    =IF(COUNTIF(A2:H2,"Pass")=COUNTA(A2:H2),"Ok","Review")

    Thanks again.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Alternative to using multiple nested IF statements

    Does that mean you removed the names adjacent to the grades?

    If not, then your new version won't work.

  7. #7
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,176

    Re: Alternative to using multiple nested IF statements

    You are correct. I was testing without take that variable into account. Thanks for pointing that out.

+ 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