Preparation of student result

%3CLINGO-SUB%20id%3D%22lingo-sub-2265034%22%20slang%3D%22en-US%22%3EPreparation%20of%20student%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2265034%22%20slang%3D%22en-US%22%3EIn%20column%20X%2C%20I%20want%20values%20return%20as%20Passed%2C%20Simple%20Passed%2C%20Promoted%20or%20Failed.%3CBR%20%2F%3EPass%20criterion%20(Pass%20in%20all%20subjects)%20-%20column%20C%20to%20column%20G%20must%20have%20scores%20greater%20than%20or%20equal%20to%2026%2C%20column%20I%20to%20column%20M%20must%20have%20scores%20greater%20than%20or%20equal%20to%207.%3CBR%20%2F%3ESimple%20Passed%20criterion%20(Fail%20in%20only%20one%20subject)%20-%20either%20only%20one%20value%20from%20column%20C%20to%20G%20must%20be%20less%20than%2026%20or%20only%20one%20value%20from%20column%20I%20to%20M%20should%20be%20less%20than%207.%3CBR%20%2F%3EPromoted%20criterion%20(Fail%20in%20two%20subjects)%20-%20either%20two%20values%20from%20column%20C%20to%20G%20must%20be%20less%20than%2026%20or%20two%20values%20from%20column%20I%20to%20M%20must%20be%20less%20than%207.%20Or%2C%20one%20value%20from%20column%20C%20to%20G%20must%20be%20less%20than%2026%20plus%20one%20value%20from%20column%20I%20to%20M%20must%20be%20less%20than%207.%20Provided%20that%20their%20grand%20total%20GT%20is%20greater%20than%20or%20equal%20to%20165%20and%20securing%2020%20marks%20in%20all%20subjects.%3CBR%20%2F%3EFailed%20-%20Failed%20in%20more%20than%20two%20subjects.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2265034%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2265568%22%20slang%3D%22de-DE%22%3ESubject%3A%20Preparation%20of%20student%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2265568%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1022237%22%20target%3D%22_blank%22%3E%40Malsawmkima%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22385%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22103%22%3Epoint%3C%2FTD%3E%3CTD%20width%3D%2245%22%3Efrom%3C%2FTD%3E%3CTD%20width%3D%2241%22%3ETo%3C%2FTD%3E%3CTD%20width%3D%22103%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2293%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Epass%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFail%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESIMPLE%20PASS%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPromoted%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Epass%3C%2FTD%3E%3CTD%3EFail%3C%2FTD%3E%3CTD%3ESIMPLE%20PASS%3C%2FTD%3E%3CTD%3EPromoted%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMil%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEd%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHs%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHc%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EPlease%20enter%20numbers%20so%20I%20can%20bypass%20all%20possible%20translation%20errors.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2265577%22%20slang%3D%22en-US%22%3ERe%3A%20Preparation%20of%20student%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2265577%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1022237%22%20target%3D%22_blank%22%3E%40Malsawmkima%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20long%20as%20I%20understood%20your%20requirement%2C%20below%20formula%20can%20be%20used%20to%20achieve%20desired%20results%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(COUNTIFS(C7%3AG7%2C%22%26gt%3B25%22)%2BCOUNTIFS(I7%3AM7%2C%22%26gt%3B6%22)%26gt%3B9%2C%22Passed%22%2CIF(COUNTIFS(C7%3AG7%2C%22%26gt%3B25%22)%2BCOUNTIFS(I7%3AM7%2C%22%26gt%3B6%22)%3D9%2C%22Simple%20Passed%22%2CIF(COUNTIFS(C7%3AG7%2C%22%26gt%3B25%22)%2BCOUNTIFS(I7%3AM7%2C%22%26gt%3B6%22)%3D8%2C%22Promoted%22%2C%22Failed%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EFor%20your%20reference%20same%20formula%20is%20also%20updated%20in%20the%20attached%20revised%20file.%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20it%20works%20for%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
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.
5 Replies

@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)

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

@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.

 

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.

@tauqeeracma 

 

Sorry, I meant to send this file instead.