Ok, this returns the count *without* taking cell G1 into account.

=SUMPRODUCT(--(MMULT((ISNUMBER(B2:D14))*(C2:C14<B2:B14)*(D2:D14< C2:C14),{1;1;1})=3))

You'll have to explain in *great detail* what the relation is to cell G1 and
give examples with expected results. I don't understand what you're doing
with cell G1. You say it can be any number =0. OK, for what purpose? If G1
=5 what is that supposed to mean?

--
Biff
Microsoft Excel MVP


"yshridhar" wrote in message
...
Yes, the count=1 and the ID nos = 1A12
With regards
Sridhar

"T. Valko" wrote:

Ok, what results would you expect with this data:

1A1.......a....81....64
1A10...65...64......a
1A11...98.....a....36
1A12...62...58....48
1A13.....a.....a....56


--
Biff
Microsoft Excel MVP


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