mode or coutif + And? how?

[makubexho]

Student--Mark---Prize
A---------81
B---------80
C---------90
A---------90
E---------59
F---------78
A---------89------Yes
B---------78
C---------80
E---------90
F---------80
B---------81
A---------90
B---------90
C---------59
D---------80
E---------79
B---------90------Yes
A---------88
D---------80
G---------79
J---------80
A---------81------Yes

a class of students (say, 40) take part in weekly tests, their scores from each test are recorded in an excel sheet as above.
the thing i want is, in column C, for each student, who score above 80 for 3 times in a row, will be given a prize.

 

[JamesW]

Hi,

Can't get the HTML board maker to work, but try this:

Col A and B contain your data
Col C contains A - J students
Col D contains: =IF(SUMPRODUCT(--($A$1:$A$23=D1)*($B$1:$B$23>80))>=3,"Prize","No Prize") - Copy down