|
Max/Min function help
Hello everybody. I maintain our school data in exce. The following is marks
data NO Sec English maths Science Social 1A1 1A 25 25 25 25 1A10 1A 25 25 24 25 1A11 1A 24 22 24 25 1A12 1A 25 25 23 24 1A13 1A 25 23 25 20 1B1 1B 20 24 23 22 1B10 1B 20 24 21 23 1B11 1B 20 18 22 23 1B12 1B 22 18 25 24 1B13 1B 20 18 23 23 1B14 1B 20 18 22 23 2A1 2A 23 23 25 24 2A10 2A 24 23 20 25 2A11 2A 24 22 24 25 2A12 2A 22 25 25 25 2A13 2A 24 25 23 25 2A14 2A 24 24 25 25 2A15 2A 22 25 25 24 2A16 2A 25 24 25 25 In sheet2 SEC(say 1B) in A1, and in B1 the subject(Maths). I want the max/min score of the subeject*sec. When i change the values in a1 or b1 it has to update. Thanks to all for your help in advance. With kind regards Sridhar |
Max/Min function help
Try these array formulas**:
MIN: =MIN(IF(Sheet1!B2:B20=A1,INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) MAX: =MAX((Sheet1!B2:B20=A1)*(INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Hello everybody. I maintain our school data in exce. The following is marks data NO Sec English maths Science Social 1A1 1A 25 25 25 25 1A10 1A 25 25 24 25 1A11 1A 24 22 24 25 1A12 1A 25 25 23 24 1A13 1A 25 23 25 20 1B1 1B 20 24 23 22 1B10 1B 20 24 21 23 1B11 1B 20 18 22 23 1B12 1B 22 18 25 24 1B13 1B 20 18 23 23 1B14 1B 20 18 22 23 2A1 2A 23 23 25 24 2A10 2A 24 23 20 25 2A11 2A 24 22 24 25 2A12 2A 22 25 25 25 2A13 2A 24 25 23 25 2A14 2A 24 24 25 25 2A15 2A 22 25 25 24 2A16 2A 25 24 25 25 In sheet2 SEC(say 1B) in A1, and in B1 the subject(Maths). I want the max/min score of the subeject*sec. When i change the values in a1 or b1 it has to update. Thanks to all for your help in advance. With kind regards Sridhar |
Max/Min function help
Thanks alot Mr. Biff for your EXCELlent formula. It works for my data and
that is what i need. With kind regards Sridhar "T. Valko" wrote: Try these array formulas**: MIN: =MIN(IF(Sheet1!B2:B20=A1,INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) MAX: =MAX((Sheet1!B2:B20=A1)*(INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Hello everybody. I maintain our school data in exce. The following is marks data NO Sec English maths Science Social 1A1 1A 25 25 25 25 1A10 1A 25 25 24 25 1A11 1A 24 22 24 25 1A12 1A 25 25 23 24 1A13 1A 25 23 25 20 1B1 1B 20 24 23 22 1B10 1B 20 24 21 23 1B11 1B 20 18 22 23 1B12 1B 22 18 25 24 1B13 1B 20 18 23 23 1B14 1B 20 18 22 23 2A1 2A 23 23 25 24 2A10 2A 24 23 20 25 2A11 2A 24 22 24 25 2A12 2A 22 25 25 25 2A13 2A 24 25 23 25 2A14 2A 24 24 25 25 2A15 2A 22 25 25 24 2A16 2A 25 24 25 25 In sheet2 SEC(say 1B) in A1, and in B1 the subject(Maths). I want the max/min score of the subeject*sec. When i change the values in a1 or b1 it has to update. Thanks to all for your help in advance. With kind regards Sridhar |
Max/Min function help
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Thanks alot Mr. Biff for your EXCELlent formula. It works for my data and that is what i need. With kind regards Sridhar "T. Valko" wrote: Try these array formulas**: MIN: =MIN(IF(Sheet1!B2:B20=A1,INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) MAX: =MAX((Sheet1!B2:B20=A1)*(INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Hello everybody. I maintain our school data in exce. The following is marks data NO Sec English maths Science Social 1A1 1A 25 25 25 25 1A10 1A 25 25 24 25 1A11 1A 24 22 24 25 1A12 1A 25 25 23 24 1A13 1A 25 23 25 20 1B1 1B 20 24 23 22 1B10 1B 20 24 21 23 1B11 1B 20 18 22 23 1B12 1B 22 18 25 24 1B13 1B 20 18 23 23 1B14 1B 20 18 22 23 2A1 2A 23 23 25 24 2A10 2A 24 23 20 25 2A11 2A 24 22 24 25 2A12 2A 22 25 25 25 2A13 2A 24 25 23 25 2A14 2A 24 24 25 25 2A15 2A 22 25 25 24 2A16 2A 25 24 25 25 In sheet2 SEC(say 1B) in A1, and in B1 the subject(Maths). I want the max/min score of the subeject*sec. When i change the values in a1 or b1 it has to update. Thanks to all for your help in advance. With kind regards Sridhar |
Max/Min function help
Mr. Biff i need a more refinement if i am not troubling. I want to put a
cutoff mark in C1 and count the number of pupil less than the cutoff mark. Can it be possible? With kind regards Sridhar "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Thanks alot Mr. Biff for your EXCELlent formula. It works for my data and that is what i need. With kind regards Sridhar "T. Valko" wrote: Try these array formulas**: MIN: =MIN(IF(Sheet1!B2:B20=A1,INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) MAX: =MAX((Sheet1!B2:B20=A1)*(INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Hello everybody. I maintain our school data in exce. The following is marks data NO Sec English maths Science Social 1A1 1A 25 25 25 25 1A10 1A 25 25 24 25 1A11 1A 24 22 24 25 1A12 1A 25 25 23 24 1A13 1A 25 23 25 20 1B1 1B 20 24 23 22 1B10 1B 20 24 21 23 1B11 1B 20 18 22 23 1B12 1B 22 18 25 24 1B13 1B 20 18 23 23 1B14 1B 20 18 22 23 2A1 2A 23 23 25 24 2A10 2A 24 23 20 25 2A11 2A 24 22 24 25 2A12 2A 22 25 25 25 2A13 2A 24 25 23 25 2A14 2A 24 24 25 25 2A15 2A 22 25 25 24 2A16 2A 25 24 25 25 In sheet2 SEC(say 1B) in A1, and in B1 the subject(Maths). I want the max/min score of the subeject*sec. When i change the values in a1 or b1 it has to update. Thanks to all for your help in advance. With kind regards Sridhar |
Max/Min function help
If I understand, try this:
=SUMPRODUCT(--(Sheet1!B2:B20=A1),--(INDEX(Sheet1!C2:F20,,MATCH(B1,Sheet1!C1:F1,0))<C1 )) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Mr. Biff i need a more refinement if i am not troubling. I want to put a cutoff mark in C1 and count the number of pupil less than the cutoff mark. Can it be possible? With kind regards Sridhar "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Thanks alot Mr. Biff for your EXCELlent formula. It works for my data and that is what i need. With kind regards Sridhar "T. Valko" wrote: Try these array formulas**: MIN: =MIN(IF(Sheet1!B2:B20=A1,INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) MAX: =MAX((Sheet1!B2:B20=A1)*(INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Hello everybody. I maintain our school data in exce. The following is marks data NO Sec English maths Science Social 1A1 1A 25 25 25 25 1A10 1A 25 25 24 25 1A11 1A 24 22 24 25 1A12 1A 25 25 23 24 1A13 1A 25 23 25 20 1B1 1B 20 24 23 22 1B10 1B 20 24 21 23 1B11 1B 20 18 22 23 1B12 1B 22 18 25 24 1B13 1B 20 18 23 23 1B14 1B 20 18 22 23 2A1 2A 23 23 25 24 2A10 2A 24 23 20 25 2A11 2A 24 22 24 25 2A12 2A 22 25 25 25 2A13 2A 24 25 23 25 2A14 2A 24 24 25 25 2A15 2A 22 25 25 24 2A16 2A 25 24 25 25 In sheet2 SEC(say 1B) in A1, and in B1 the subject(Maths). I want the max/min score of the subeject*sec. When i change the values in a1 or b1 it has to update. Thanks to all for your help in advance. With kind regards Sridhar |
Max/Min function help
Marvelous! Mr. Biff my sincere thanks to you. Exactly same i want.
With kind regards Sridhar "T. Valko" wrote: If I understand, try this: =SUMPRODUCT(--(Sheet1!B2:B20=A1),--(INDEX(Sheet1!C2:F20,,MATCH(B1,Sheet1!C1:F1,0))<C1 )) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Mr. Biff i need a more refinement if i am not troubling. I want to put a cutoff mark in C1 and count the number of pupil less than the cutoff mark. Can it be possible? With kind regards Sridhar "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Thanks alot Mr. Biff for your EXCELlent formula. It works for my data and that is what i need. With kind regards Sridhar "T. Valko" wrote: Try these array formulas**: MIN: =MIN(IF(Sheet1!B2:B20=A1,INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) MAX: =MAX((Sheet1!B2:B20=A1)*(INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Hello everybody. I maintain our school data in exce. The following is marks data NO Sec English maths Science Social 1A1 1A 25 25 25 25 1A10 1A 25 25 24 25 1A11 1A 24 22 24 25 1A12 1A 25 25 23 24 1A13 1A 25 23 25 20 1B1 1B 20 24 23 22 1B10 1B 20 24 21 23 1B11 1B 20 18 22 23 1B12 1B 22 18 25 24 1B13 1B 20 18 23 23 1B14 1B 20 18 22 23 2A1 2A 23 23 25 24 2A10 2A 24 23 20 25 2A11 2A 24 22 24 25 2A12 2A 22 25 25 25 2A13 2A 24 25 23 25 2A14 2A 24 24 25 25 2A15 2A 22 25 25 24 2A16 2A 25 24 25 25 In sheet2 SEC(say 1B) in A1, and in B1 the subject(Maths). I want the max/min score of the subeject*sec. When i change the values in a1 or b1 it has to update. Thanks to all for your help in advance. With kind regards Sridhar |
Max/Min function help
You're welcome!
-- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Marvelous! Mr. Biff my sincere thanks to you. Exactly same i want. With kind regards Sridhar "T. Valko" wrote: If I understand, try this: =SUMPRODUCT(--(Sheet1!B2:B20=A1),--(INDEX(Sheet1!C2:F20,,MATCH(B1,Sheet1!C1:F1,0))<C1 )) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Mr. Biff i need a more refinement if i am not troubling. I want to put a cutoff mark in C1 and count the number of pupil less than the cutoff mark. Can it be possible? With kind regards Sridhar "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Thanks alot Mr. Biff for your EXCELlent formula. It works for my data and that is what i need. With kind regards Sridhar "T. Valko" wrote: Try these array formulas**: MIN: =MIN(IF(Sheet1!B2:B20=A1,INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) MAX: =MAX((Sheet1!B2:B20=A1)*(INDEX(Sheet1!C2:F20,,MATC H(B1,Sheet1!C1:F1,0)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Hello everybody. I maintain our school data in exce. The following is marks data NO Sec English maths Science Social 1A1 1A 25 25 25 25 1A10 1A 25 25 24 25 1A11 1A 24 22 24 25 1A12 1A 25 25 23 24 1A13 1A 25 23 25 20 1B1 1B 20 24 23 22 1B10 1B 20 24 21 23 1B11 1B 20 18 22 23 1B12 1B 22 18 25 24 1B13 1B 20 18 23 23 1B14 1B 20 18 22 23 2A1 2A 23 23 25 24 2A10 2A 24 23 20 25 2A11 2A 24 22 24 25 2A12 2A 22 25 25 25 2A13 2A 24 25 23 25 2A14 2A 24 24 25 25 2A15 2A 22 25 25 24 2A16 2A 25 24 25 25 In sheet2 SEC(say 1B) in A1, and in B1 the subject(Maths). I want the max/min score of the subeject*sec. When i change the values in a1 or b1 it has to update. Thanks to all for your help in advance. With kind regards Sridhar |
| All times are GMT +1. The time now is 10:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com