fastread homefastrread library fastread menu

Excel : how to create marksheet

Tutorial by:Sandy      Date: 2018-01-15 05:55:05

❰ Previous Next ❱

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)

❰ Previous Next ❱


Excel

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website