Hello everybody. The following is the data
NO Test-1 Test-2 Test-3
1A1 40 81 64
1A10 38 64 38
1A11 98 56 36
1A12 62 58 48
1A13 a 73 56
1A14 78 40 30
1A15 56 45 36
1A16 20 a 36
1A17 86 85 56
1A18 46 40 48
1A19 44 35 a
1A2 50 30 20
1A20 76 a 80

=SUMPRODUCT((($C$2:$C$14)-($B$2:$B$14)<$G$1)*(($D$2:$D$14)-($C$2:$C$14)<$G$1))
It calculates the number of pupil who scores are in decreasing order from
test-1 to test-3
=IF(ROWS($1:1)<=$G$3,INDEX($A$2:$A$14,SMALL(IF((($ C$2:$C$14)-($B$2:$B$14)<$G$1)*(($D$2:$D$14)-($C$2:$C$14)<$G$1),ROW($A$2:$A$14)-MIN(ROW($B$2:B$14))+1),ROWS($1:1))),"")
It lists the Idno.s of the pupil based onthe above criteria
The formulae resulting errors when there are some absents-"a" in the marks.
Is there any way to modify these formulae to get the required results?
With regards
Sridhar