Results 1 to 4 of 4

Thread: need help with logical formulas

  1. #1

    Exclamation need help with logical formulas



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone,
    I'm new here and a fairly basic user of Excel so would appreciate patience and 'slow talking'
    i need to find a formula that calculates a grade based on several variables as follows:

    if A1>=80 and A2>50 and A3>50 and A4>50 and A5>50, then the grade is A; if A1>76 but <80 and any of A2 to A5<50, then the grade is B; if A1=<66, then the grade is C.

    I hope this make sense. I've been trying to use the IF formulas but but keep getting the message it's incorrect, or too many variables, etc.

    is there a way to calculate the outcome that would be an overall grade for a subject?

    A1 is average attendance at subjects A2, A3, A4 and A5 combined. So to get an A the student needs to have over 80 in overall attendance and 50 in each element. to get B he needs overall attendance between 67 and 79 and only one of the A2, A3, A4 and A5 below 50. to get C we only look at overall attendance below 66.

    I would be grateful for any help you could offer.

    many thanks.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =IF(AND(A1>=80,COUNTIF(A2:A5,">50")=4),"A",IF(AND(A1>76,A1<80,COUNTIF(A2:A5,"<50")>0),"B",IF(A1<=66,"C","")))


  3. #3
    It's worked brillliantly!! Thanks so much NBVC.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    You are welcome.

    Looking at it again, I guess you wanted the last part, for "C" to be <=76...


Posting Permissions

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