+ Reply to Thread
Results 1 to 6 of 6

Using the formulas AND, IF, OR together

  1. #1
    Registered User
    Join Date
    10-05-2011
    Location
    Mexico City
    MS-Off Ver
    Excel 2010
    Posts
    5

    Using the formulas AND, IF, OR together

    Dear everyone,

    I got stuck in one exercise and I hope you can help me.

    This is the case: I have a list of people that obtained 4 different grades and the minimum grade to pass a module is 50. I now have to calculate which people did exactly pass 3 out of 4 modules by using the formulas AND,IF,OR. I really don't understand how to calculate it. I have added the excel file. Thanks in advance
    Attached Files Attached Files
    Last edited by LJ25; 10-06-2011 at 12:18 PM.

  2. #2
    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,087

    Re: Using the formulas AND, IF, OR together

    One way:

    =IF(SUMPRODUCT(--($B5:$E5>=$D$3))=3,"yes","no")

    You could use a similar formula for the other options (one or all(4))

    Regards
    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


  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,087

    Re: Using the formulas AND, IF, OR together

    All: =IF(SUMPRODUCT(--($B5:$E5>=$D$3))=4,"yes","no")

    >=1: =IF(SUMPRODUCT(--($B5:$E5>=$D$3))>=1,"yes","no")

  4. #4
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Using the formulas AND, IF, OR together

    Try this. It works. I ahve also attached your workbook with the formula in column H.

    Please Login or Register  to view this content.
    If you could use other functions like COUNT, the formula could be simpler.

    modytrane.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-05-2011
    Location
    Mexico City
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Using the formulas AND, IF, OR together

    Thanks for your help!! I have one question left What exactly does the part of the formula (-- mean ??

  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,087

    Re: Using the formulas AND, IF, OR together

    You're welcome. Thanks for the rep.

    The double negative just coerces a numeric value. If you don't have it, you get a zero result.

    Regards

+ 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