The formulae works when the "a" is replaced by either a blank or a number.
What i want is a modification of formulae to work when there are "a".
G1 - may be any number = 0.
The criteria i mean is the the list of the pupil whose scores are in
decreasing order from test-1 to test-3
With the following data i am getting 6 and the id nos are
1a11,1a12,1a14,1a15,1a17, 1a2
NO Test-1 Test-2 Test-3
1A1 40 81 64
1A10 38 64 38
1A11 98 56 36
1A12 62 58 48
1A13 36 73 56
1A14 78 40 30
1A15 56 45 36
1A16 20 25 36
1A17 86 85 56
1A18 46 40 48
1A19 44 35 50
1A2 50 30 20
1A20 76 83 80
Any further information needed Mr. Biff.
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