Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula modification
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula modification
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula modification
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula modification
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula modification
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula modification
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula modification
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula modification
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula modification
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula modification
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 "T. Valko" wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula modification
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 "yshridhar" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for last modification date? | Excel Discussion (Misc queries) | |||
Formula Modification | Excel Discussion (Misc queries) | |||
Formula Modification | New Users to Excel | |||
Formula Modification | Excel Worksheet Functions | |||
Formula Modification Help | Excel Worksheet Functions |