How to find best and worst grade?

[Aivis]

Hi all!

My problem is current:
I want to find best student grade from a list (see below) and worst student
grade from the list, but I can' t do it if student is more than 2 times in
this list.
In column E, I use following formula:
=OFFSET($I$1;MATCH(MAX(C2;D2-C2);$J$2:$J$7;0);0), but in column F:
=OFFSET($I$1;MATCH(IF(MIN(C2;D2-C2)=0;C2;MIN(C2;D2-C2));$J$2:$J$7;0);0).
These formula is OK when student have one or two records in the list. But
how can I get right answer if student have more than 2 records?

A B C D E F G H
1 Name Grade1 Grade2 SUMIF Worst1 Best1 Worst2 Best2
2 Joe B 2 6 D B C A
3 Alice B 2 9 #N/A B D B
4 John C 3 3 C C C C
5 Alice C 3 9 F C D B
6 Josef A 1 1 A A A A
7 Philip F 6 6 F F F F
8 Karin D 4 6 D B D B
9 Karin B 2 6 D B D B
10 Joe C 3 6 C C C A
11 David C 3 5 C B C B
12 Joe A 1 6 E A C A
13 David B 2 5 C B C B
14 Alice D 4 9 E D D B

I J
1
2 A 1
3 B 2
4 C 3
5 D 4
6 E 5
7 F 6

*Column C is students' grade in number format (used VLOOKUP formula).
**Column E and F shows students' worst/best grade calculated by formula;
***Column G and H shows students' worst/best grade (how it should be;
inserted manually).

 

[CLR]

You might try using a Pivot Table to get the results you're after. Give it a
a try, and if you need help, post back.

Vaya con Dios,
Chuck, CABGx3

 

[Aivis]

I suppose, that Pivot Table would solve this problem. But how solve this
problem not using Pivot Table?

 

[CLR]

Try these ARRAY formulas, modified as necessary for your range....

=MAX(IF(A2:A14="alice",C2:C14))
=MIN(IF(A2:A14="alice",C2:C14))

Remember, array=entered with Ctrl-Shift-Enter

Vaya con Dios,
Chuck, CABGx3

 

[Aivis]

Maybe I do not understand you completely, but I tried to use your solution,
but it doesn' t work.
If I use current formula: ={MAX(IF(A2:A14=A2;C2:C14))} then formula shows
max value only to student Joe in all array. If I change A2 to A3, then result
in all K column cells will be 4 (Max value to student Alice).
So, I need in other way deffine cell A2 to find max value of grade to each
student.

A C K
1 Name Grade2 MAX
2 Joe 2 3
3 Alice 2 3
4 John 3 3
5 Alice 3 3
6 Josef 1 3
7 Philip 6 3
8 Karin 4 3
9 Karin 2 3
10 Joe 3 3
11 David 3 3
12 Joe 1 3
13 David 2 3
14 Alice 4 3

 

[CLR]

Use Absolute addresses if copying down

=MAX(IF($A$2:$A$14=A2,$C$2:$C$14))

=MIN(IF($A$2:$A$14=A2,$C$2:$C$14))

Still array-entered

Vaya con Dios,
Chuck, CABGx3