sorry once again G1 =0 for the data i have provided which is resulting the
count 6
With regards
Sridhar


"T. Valko" wrote:

Tell us what's in G1 and which of NO's meet the conditions.

Sorry for inadequate information. G1 - is the user entered value related
to
the differece between the test scores.

You didn't provide the requested information!

What you're wanting to do is fairly complicated and without *fully*
understanding what you want to do you may not get any helpful suggestions.

Enter a value in G1 that you would typically enter - tell us what that value
is.

Then tell us which NO's meet the criteria - list them.

I replaced the "A's" with random numbers and entered 50 in cell G1. The
formula returned 12. Clearly, there were not 12 NO's that met your
description of:

It calculates the number of pupil who scores are in decreasing order from
test-1 to test-3


--
Biff
Microsoft Excel MVP


"yshridhar" wrote in message
...
Sorry for inadequate information. G1 - is the user entered value related
to
the differece between the test scores.
With regards
Sridhar

"T. Valko" wrote:

Tell us what's in G1 and which of NO's meet the conditions.

--
Biff
Microsoft Excel MVP


"yshridhar" wrote in message
...
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