☄ EXPOLORE MIND ⚜ Tutorial ? JIQA Biography News
PRV MENU NEXT

how to create marksheet

Course- Excel >

Here is a method that works. However it is nigh impossible to get an E to turn up next to a score for Fail-Exempt, because adding a column for the E will create circular references.

Excel 2010

  A B C D E F G H I J K L M N O P
1   Exam scores                            
2                                
3   Exam1           Exam2             Result  
4   P1 P2 P3 P4 Tot1   P1 P2 P3 P4 Tot2 TotOv   Exam1 Exam2
5 H Grant 40 50 40 70 200           0 200   Pass  
6 S Johansson         0   50 60 60 40 210 210     Pass
7 O Bloom 30 60 80 50 220   30 30 80 80 220 440   Fail-Ex Fail
8 J Depp 40 40 40 40 160   40 40 40 40 160 320   Fail Fail
9 B Willis 60 60 50 70 240   40 80 80 60 260 500   Pass Pass
10 F Astaire 30 80 60 60 230   40 80 80 50 250 480   Fail-Ex Pass
11 N. Jean 60 40     100   30       30 130      

Sheet3



Worksheet Formulas
Cell Formula
L5 =SUM(H5:K5)
M5 =+L5+F5
L6 =SUM(H6:K6)
M6 =+L6+F6
L7 =SUM(H7:K7)
M7 =+L7+F7
L8 =SUM(H8:K8)
M8 =+L8+F8
L9 =SUM(H9:K9)
M9 =+L9+F9
L10 =SUM(H10:K10)
M10 =+L10+F10
L11 =SUM(H11:K11)
M11 =+L11+F11
O5 =IF(COUNT(B5:E5)<4,"",IF(AND(MIN(B5:E5)>=40,SUM(B5:E5)>=200),"Pass",IF(AND(MIN(B5:E5)>=30,MAX(B5:E5)>=60,COUNTIF(B5:E5,"=30")=1,SUM(B5:E5)>=200),"Fail-Ex","Fail")))
P5 =IF(COUNT(H5:K5)<4,"",IF(AND(MIN(H5:K5)>=40,SUM(H5:K5)>=200),"Pass",IF(AND(MIN(H5:K5)>=30,MAX(H5:K5)>=60,COUNTIF(H5:K5,"=30")=1,SUM(H5:K5)>=200),"Fail-Ex","Fail")))
O6 =IF(COUNT(B6:E6)<4,"",IF(AND(MIN(B6:E6)>=40,SUM(B6:E6)>=200),"Pass",IF(AND(MIN(B6:E6)>=30,MAX(B6:E6)>=60,COUNTIF(B6:E6,"=30")=1,SUM(B6:E6)>=200),"Fail-Ex","Fail")))
P6 =IF(COUNT(H6:K6)<4,"",IF(AND(MIN(H6:K6)>=40,SUM(H6:K6)>=200),"Pass",IF(AND(MIN(H6:K6)>=30,MAX(H6:K6)>=60,COUNTIF(H6:K6,"=30")=1,SUM(H6:K6)>=200),"Fail-Ex","Fail")))
O7 =IF(COUNT(B7:E7)<4,"",IF(AND(MIN(B7:E7)>=40,SUM(B7:E7)>=200),"Pass",IF(AND(MIN(B7:E7)>=30,MAX(B7:E7)>=60,COUNTIF(B7:E7,"=30")=1,SUM(B7:E7)>=200),"Fail-Ex","Fail")))
P7 =IF(COUNT(H7:K7)<4,"",IF(AND(MIN(H7:K7)>=40,SUM(H7:K7)>=200),"Pass",IF(AND(MIN(H7:K7)>=30,MAX(H7:K7)>=60,COUNTIF(H7:K7,"=30")=1,SUM(H7:K7)>=200),"Fail-Ex","Fail")))
O8 =IF(COUNT(B8:E8)<4,"",IF(AND(MIN(B8:E8)>=40,SUM(B8:E8)>=200),"Pass",IF(AND(MIN(B8:E8)>=30,MAX(B8:E8)>=60,COUNTIF(B8:E8,"=30")=1,SUM(B8:E8)>=200),"Fail-Ex","Fail")))
P8 =IF(COUNT(H8:K8)<4,"",IF(AND(MIN(H8:K8)>=40,SUM(H8:K8)>=200),"Pass",IF(AND(MIN(H8:K8)>=30,MAX(H8:K8)>=60,COUNTIF(H8:K8,"=30")=1,SUM(H8:K8)>=200),"Fail-Ex","Fail")))
O9 =IF(COUNT(B9:E9)<4,"",IF(AND(MIN(B9:E9)>=40,SUM(B9:E9)>=200),"Pass",IF(AND(MIN(B9:E9)>=30,MAX(B9:E9)>=60,COUNTIF(B9:E9,"=30")=1,SUM(B9:E9)>=200),"Fail-Ex","Fail")))
P9 =IF(COUNT(H9:K9)<4,"",IF(AND(MIN(H9:K9)>=40,SUM(H9:K9)>=200),"Pass",IF(AND(MIN(H9:K9)>=30,MAX(H9:K9)>=60,COUNTIF(H9:K9,"=30")=1,SUM(H9:K9)>=200),"Fail-Ex","Fail")))
O10 =IF(COUNT(B10:E10)<4,"",IF(AND(MIN(B10:E10)>=40,SUM(B10:E10)>=200),"Pass",IF(AND(MIN(B10:E10)>=30,MAX(B10:E10)>=60,COUNTIF(B10:E10,"=30")=1,SUM(B10:E10)>=200),"Fail-Ex","Fail")))
P10 =IF(COUNT(H10:K10)<4,"",IF(AND(MIN(H10:K10)>=40,SUM(H10:K10)>=200),"Pass",IF(AND(MIN(H10:K10)>=30,MAX(H10:K10)>=60,COUNTIF(H10:K10,"=30")=1,SUM(H10:K10)>=200),"Fail-Ex","Fail")))
O11 =IF(COUNT(B11:E11)<4,"",IF(AND(MIN(B11:E11)>=40,SUM(B11:E11)>=200),"Pass",IF(AND(MIN(B11:E11)>=30,MAX(B11:E11)>=60,COUNTIF(B11:E11,"=30")=1,SUM(B11:E11)>=200),"Fail-Ex","Fail")))
P11 =IF(COUNT(H11:K11)<4,"",IF(AND(MIN(H11:K11)>=40,SUM(H11:K11)>=200),"Pass",IF(AND(MIN(H11:K11)>=30,MAX(H11:K11)>=60,COUNTIF(H11:K11,"=30")=1,SUM(H11:K11)>=200),"Fail-Ex","Fail")))
F5 =SUM(B5:E5)
F6 =SUM(B6:E6)
F7 =SUM(B7:E7)
F8 =SUM(B8:E8)
F9 =SUM(B9:E9)
F10 =SUM(B10:E10)
F11 =SUM(B11:E11)

 

PRV MENU NEXT