Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula debugging
I am sorry to repost this query. The following is my data
No English Maths Science Social 1A1 64 28 40 32 1a1 38 10 27 16 1A11 98 80 80 60 1A12 48 30 36 30 1A13 56 60 26 36 1A14 60 44 51 68 1A15 a a a a 1A16 36 6 7 6 1A17 56 44 52 48 1A18 48 26 18 36 1A19 50 42 41 42 1A2 46 38 31 24 1A20 80 66 70 61 1A21 a a 28 20 1A22 36 10 12 22 =SUMPRODUCT(--(B2:B16=G1)*--(C2:C16=G1)*--(D2:D16=G1)*--(E2:E16=G1)) It calculates the no. of pupil getting more than a mark in G1 in all the subjects. However this formula also includes the IDno. with all "a"s. =IF(ROWS($1:1)<=$F$2,INDEX($A$2:$A$16,SMALL(IF(($B $2:$B$16=$G$1)*($C$2:$C$16=$G$1)*($D$2:$D$16=$G $1)*($E$2:$E$16=$G$1),ROW($B$2:$B$16)-MIN(ROW($A$2:$A$16))+1),ROWS($1:1))),"") This array formula returns the IDno.s of the pupil who secured more than a mark in G1 in all the subjects. But here also same problem; it is retuning the IDno with all "a"s. In case of "<" i am not getting this sort of problem. Only in case of "" the formula fails. Any suggestions, pls. With regards Sridhar |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula debugging
Try these:
For the count: =SUMPRODUCT(--(MMULT((ISNUMBER(B2:E16))*(B2:E16=G1),{1;1;1;1})= 4)) To extract those that meet the criteria (array entered): =IF(ROWS($1:1)<=$F$2,INDEX($A$2:$A$16,SMALL(IF(MMU LT((ISNUMBER(B$2:E$16))*(B$2:E$16=G$1),{1;1;1;1}) =4,ROW($B$2:$B$16)-MIN(ROW($A$2:$A$16))+1),ROWS($1:1))),"") There might be a better way to write that formula but I don't have time to play with it right now. -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... I am sorry to repost this query. The following is my data No English Maths Science Social 1A1 64 28 40 32 1a1 38 10 27 16 1A11 98 80 80 60 1A12 48 30 36 30 1A13 56 60 26 36 1A14 60 44 51 68 1A15 a a a a 1A16 36 6 7 6 1A17 56 44 52 48 1A18 48 26 18 36 1A19 50 42 41 42 1A2 46 38 31 24 1A20 80 66 70 61 1A21 a a 28 20 1A22 36 10 12 22 =SUMPRODUCT(--(B2:B16=G1)*--(C2:C16=G1)*--(D2:D16=G1)*--(E2:E16=G1)) It calculates the no. of pupil getting more than a mark in G1 in all the subjects. However this formula also includes the IDno. with all "a"s. =IF(ROWS($1:1)<=$F$2,INDEX($A$2:$A$16,SMALL(IF(($B $2:$B$16=$G$1)*($C$2:$C$16=$G$1)*($D$2:$D$16=$G $1)*($E$2:$E$16=$G$1),ROW($B$2:$B$16)-MIN(ROW($A$2:$A$16))+1),ROWS($1:1))),"") This array formula returns the IDno.s of the pupil who secured more than a mark in G1 in all the subjects. But here also same problem; it is retuning the IDno with all "a"s. In case of "<" i am not getting this sort of problem. Only in case of "" the formula fails. Any suggestions, pls. With regards Sridhar |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula debugging
Thanks alot Mr. Biff. The formulae modification solved the problem. Hoping
that you may get some time from your valuable seconds to refine the formula. With regards Sridhar "T. Valko" wrote: Try these: For the count: =SUMPRODUCT(--(MMULT((ISNUMBER(B2:E16))*(B2:E16=G1),{1;1;1;1})= 4)) To extract those that meet the criteria (array entered): =IF(ROWS($1:1)<=$F$2,INDEX($A$2:$A$16,SMALL(IF(MMU LT((ISNUMBER(B$2:E$16))*(B$2:E$16=G$1),{1;1;1;1}) =4,ROW($B$2:$B$16)-MIN(ROW($A$2:$A$16))+1),ROWS($1:1))),"") There might be a better way to write that formula but I don't have time to play with it right now. -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... I am sorry to repost this query. The following is my data No English Maths Science Social 1A1 64 28 40 32 1a1 38 10 27 16 1A11 98 80 80 60 1A12 48 30 36 30 1A13 56 60 26 36 1A14 60 44 51 68 1A15 a a a a 1A16 36 6 7 6 1A17 56 44 52 48 1A18 48 26 18 36 1A19 50 42 41 42 1A2 46 38 31 24 1A20 80 66 70 61 1A21 a a 28 20 1A22 36 10 12 22 =SUMPRODUCT(--(B2:B16=G1)*--(C2:C16=G1)*--(D2:D16=G1)*--(E2:E16=G1)) It calculates the no. of pupil getting more than a mark in G1 in all the subjects. However this formula also includes the IDno. with all "a"s. =IF(ROWS($1:1)<=$F$2,INDEX($A$2:$A$16,SMALL(IF(($B $2:$B$16=$G$1)*($C$2:$C$16=$G$1)*($D$2:$D$16=$G $1)*($E$2:$E$16=$G$1),ROW($B$2:$B$16)-MIN(ROW($A$2:$A$16))+1),ROWS($1:1))),"") This array formula returns the IDno.s of the pupil who secured more than a mark in G1 in all the subjects. But here also same problem; it is retuning the IDno with all "a"s. In case of "<" i am not getting this sort of problem. Only in case of "" the formula fails. Any suggestions, pls. With regards Sridhar |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula debugging
I think that's about it unless you want to use a column of helper formulas.
-- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Thanks alot Mr. Biff. The formulae modification solved the problem. Hoping that you may get some time from your valuable seconds to refine the formula. With regards Sridhar "T. Valko" wrote: Try these: For the count: =SUMPRODUCT(--(MMULT((ISNUMBER(B2:E16))*(B2:E16=G1),{1;1;1;1})= 4)) To extract those that meet the criteria (array entered): =IF(ROWS($1:1)<=$F$2,INDEX($A$2:$A$16,SMALL(IF(MMU LT((ISNUMBER(B$2:E$16))*(B$2:E$16=G$1),{1;1;1;1}) =4,ROW($B$2:$B$16)-MIN(ROW($A$2:$A$16))+1),ROWS($1:1))),"") There might be a better way to write that formula but I don't have time to play with it right now. -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... I am sorry to repost this query. The following is my data No English Maths Science Social 1A1 64 28 40 32 1a1 38 10 27 16 1A11 98 80 80 60 1A12 48 30 36 30 1A13 56 60 26 36 1A14 60 44 51 68 1A15 a a a a 1A16 36 6 7 6 1A17 56 44 52 48 1A18 48 26 18 36 1A19 50 42 41 42 1A2 46 38 31 24 1A20 80 66 70 61 1A21 a a 28 20 1A22 36 10 12 22 =SUMPRODUCT(--(B2:B16=G1)*--(C2:C16=G1)*--(D2:D16=G1)*--(E2:E16=G1)) It calculates the no. of pupil getting more than a mark in G1 in all the subjects. However this formula also includes the IDno. with all "a"s. =IF(ROWS($1:1)<=$F$2,INDEX($A$2:$A$16,SMALL(IF(($B $2:$B$16=$G$1)*($C$2:$C$16=$G$1)*($D$2:$D$16=$G $1)*($E$2:$E$16=$G$1),ROW($B$2:$B$16)-MIN(ROW($A$2:$A$16))+1),ROWS($1:1))),"") This array formula returns the IDno.s of the pupil who secured more than a mark in G1 in all the subjects. But here also same problem; it is retuning the IDno with all "a"s. In case of "<" i am not getting this sort of problem. Only in case of "" the formula fails. Any suggestions, pls. With regards Sridhar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Debugging runtime errors | Excel Discussion (Misc queries) | |||
change value while debugging | Excel Discussion (Misc queries) | |||
Need some debugging help, please | Excel Discussion (Misc queries) | |||
Excel VBA debugging help needed! | Excel Discussion (Misc queries) | |||
Startup Error debugging help | Excel Discussion (Misc queries) |