Max/Min function help

[yshridhar]

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

 

[T. Valko]

Try these array formulas**:

MIN:

=MIN(IF(Sheet1!B2:B20=A1,INDEX(Sheet1!C2:F20,,MATCH(B1,Sheet1!C1:F1,0))))

MAX:

=MAX((Sheet1!B2:B20=A1)*(INDEX(Sheet1!C2:F20,,MATCH(B1,Sheet1!C1:F1,0))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[yshridhar]

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]

You're welcome. Thanks for the feedback!

 

[yshridhar]

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]

If I understand, try this:

=SUMPRODUCT(--(Sheet1!B2:B20=A1),--(INDEX(Sheet1!C2:F20,,MATCH(B1,Sheet1!C1:F1,0))<C1))

 

[yshridhar]

Marvelous! Mr. Biff my sincere thanks to you. Exactly same i want.
With kind regards
Sridhar

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Marvelous! Mr. Biff my sincere thanks to you. Exactly same i want.
With kind regards
Sridhar