Forum Discussion

Malsawmkima's avatar
Malsawmkima
Copper Contributor
Apr 10, 2021

Preparation of student result

In column X, I want values return as Passed, Simple Passed, Promoted or Failed.
Pass criterion (Pass in all subjects) - column C to column G must have scores greater than or equal to 26, column I to column M must have scores greater than or equal to 7.
Simple Passed criterion (Fail in only one subject) - either only one value from column C to G must be less than 26 or only one value from column I to M should be less than 7.
Promoted criterion (Fail in two subjects) - either two values from column C to G must be less than 26 or two values from column I to M must be less than 7. Or, one value from column C to G must be less than 26 plus one value from column I to M must be less than 7. Provided that their grand total GT is greater than or equal to 165 and securing 20 marks in all subjects.
Failed - Failed in more than two subjects.
excel
Formulas and Functions
office 365

5 Replies

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Apr 10, 2021

    Hi Malsawmkima 

    As long as I understood your requirement, below formula can be used to achieve desired results:

     

    =IF(COUNTIFS(C7:G7,">25")+COUNTIFS(I7:M7,">6")>9,"Passed",IF(COUNTIFS(C7:G7,">25")+COUNTIFS(I7:M7,">6")=9,"Simple Passed",IF(COUNTIFS(C7:G7,">25")+COUNTIFS(I7:M7,">6")=8,"Promoted","Failed")))

    For your reference same formula is also updated in the attached revised file.

    Please let me know if it works for you

     

    Thanks

    Tauqeer

    • Malsawmkima's avatar
      Malsawmkima
      Copper Contributor
      Apr 10, 2021

      tauqeeracma 

       

      Thank you for your reply. unfortunately, it didn't work. Perhaps i didn't make it clear. The pass mark for the external exam for each subject is 26 (column c to g) while for internal exam is 7 (column i to m). So, a student must attain pass mark in all subjects both for the external and internal exams to be Passed.

      If a student fails in only one subject overall (either in the external i.e. from column c to g or internal exam i.e. from column i to m), he is to be considered Simple Passed.

      If a student fails in two subjects in the external exam or two subjects in the internal exam or one each from the external and internal exam, but secures a grand total of 165 marks and also securing at least 16 marks in the external exam in each subject and at least 4 marks in the internal exam in each subject will be promoted.

      if above conditions are not met, then, Failed.

       

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Apr 10, 2021

    Malsawmkima 

    Pointfrom to  
    PASS??  
    FAIL??  
    SIMPLE PASS??  
    PROMOTED??  
         
     PASSFAILSIMPLE PASSPROMOTED
    E????
    MIL    
    H    
    Ed    
    Hs    
    Hc    

     

    Please enter numbers so I can bypass all possible translation errors.

     

    Thank you for your understanding and patience

     

     

    Nikolino

    I know I don't know anything (Socrates)

    • Malsawmkima's avatar
      Malsawmkima
      Copper Contributor
      Apr 10, 2021
      Thank you for the reply, but, your table is not appropriate to my needs.

      The pass mark for the external exam for each subject is 26 (column c to g) while for internal exam is 7 (column i to m). So, a student must attain pass mark in all subjects both for the external and internal exams to be Passed.

      If a student fails in only one subject overall (either in the external i.e. from column c to g or internal exam i.e. from column i to m), he is to be considered Simple Passed.

      If a student fails in two subjects in the external exam or two subjects in the internal exam or one each from the external and internal exam, but secures a grand total of 165 marks and also securing at least 16 marks in the external exam in each subject and at least 4 marks in the internal exam in each subject will be promoted.

      If above conditions are not met, then, Failed.

Resources