what functions to use in my case?

[makubexho]

annoying case, here it is, a class of students (say, 40) take part in weekly test regularly, their scores from each test are recorded in an excel sheet as following,
(for simplicity, i only use 2 students' records, peter's and kitty's)
peter---85
kitty----80
peter---89
kitty----79
peter---90---prize
kitty----88
peter---81
kitty---80
peter---87
kitty---91---prize
peter---84---prize

the thing i want to do is, in column C, for each student, who score above 80 for 3 times in a row, will be given a prize (e.g. peter, he is prized at his 3rd test becoz he fulfills this requirement, but Kitty, she failed it at 2nd test (scored 79 only), so the streak breaks there. She is prized at the 5th test as she scored above 80 for 3 times in a row.

 

[p45cal]

Excel Workbook
	A	B	C
1	peter	85
2	kitty	80
3	peter	89
4	kitty	79
5	peter	90	Prize
6	kitty	88
7	peter	81
8	kitty	80
9	peter	87
10	kitty	91	Prize
11	peter	84	Prize
Sheet

fomula in C5 and copy down.

 

[makubexho]

thx but not quite close to what i m looking for, be more specific, look at this table for details of the case

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


Can you notice the pattern in which the prize is given?

 

[p45cal]

Can you notice the pattern in which the prize is given?

Yes, and I note that you removed the pattern of students you had.
You're asking for a formula (and it won't be a simple one) to compensate for a sloppy data layout.
Your data is laid out like a database, 1 record per row, so it's more suitable to being interrogated that way, with pivot tables, advanced filters and the like. Perhaps over the top for something like this. I suggest you devote a row (or column) or two per student, so that all the info about a given student is all in one area, that'll make it easier to construct formulae.