Formula modification

[yshridhar]

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

 

[T. Valko]

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

 

[yshridhar]

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

 

[T. Valko]

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

 

[yshridhar]

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

 

[yshridhar]

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

 

[T. Valko]

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

 

[yshridhar]

Yes, the count=1 and the ID nos = 1A12
With regards
Sridhar

 

[T. Valko]

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

=SUMPRODUCT(--(MMULT((ISNUMBER(B214))*(C2:C14<B2:B14)*(D214<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?

 

[yshridhar]

Thanks you very much Mr. Biff. Using G1 value we categorise the students.
Like students who scores are below G1=5 or G1=10 we separate them and we
train them separately to improve their skills. We can't impart same training
for a student who <5 and or <10 or 20.

One humble request. If the data you have given me results count =3 and Id
nos = 1a1, 1a10, 1a12 what is the resultant formula.
I mean to say that the student has to take two exams continuously.
In an year we conduct seven tests. Can i extend the same formula if the
data is appended likewise.
My sincere and heartfelt thanks to you Mr. Biff
With regards
Sridhar

 

[yshridhar]

Let me give more information on G1.
I have slightly modified the formula. Instead of (test2-test1)<G1 ,
diff(test1-test1)>G1. The following is the data
NO Test-1 Test-2 Test-3
1A1 48 81 64
1A10 38 64 38
1A11 66 56 36
1A12 62 58 48
1A13 36 73 78
1A14 78 40 30
1A15 56 46 36
1A16 20 25 36
1A17 86 85 56
1A18 46 40 48
1A19 44 35 50
1A2 50 35 20
1A20 76 83 80

When G1 = 0, count = 6. G1= 1, count = 4 (1a11,1a14, 1a15, 1a2) and G1 = 10,
count = 1 (1a2).
This is my idea that we can analyse more about the student data when G1
instead of just comparing the two value of the tests.
I hope that i could able to answer atleast to some extent of G1.
One more explanation is that I am not well versed with all the functions.
Our requirement and my knowledge of the functions prompted me to write the
formula.
With regards
Sridhar