Try these array formulas** :

=MAX(IF(B$3:B$15=B3,C$3:C$15))

=MIN(IF(B$3:B$15=B3,C$3:C$15))

Copy down as needed.

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

--
Biff
Microsoft Excel MVP


"Aivis" wrote in message
...

Is there a better way to find best/worst student grade from a list (see
below), not using PivotTable.

B C D E F G
2 Name Grade COUNTIF Array Worst Best
3 Alice 5 3 C3:C5 5 3
4 Alice 3 3 C3:C5 5 3
5 Alice 4 3 C3:C5 5 3
6 David 3 2 C6:C7 3 2
7 David 2 2 C6:C7 3 2
8 Joe 2 3 C8:C10 6 1
9 Joe 6 3 C8:C10 6 1
10 Joe 1 3 C8:C10 6 1
11 John 3 1 C11:C11 3 3
12 Josef 1 1 C12:C12 1 1
13 Karin 4 2 C13:C14 4 2
14 Karin 2 2 C13:C14 4 2
15 Philip 5 1 C15:C15 5 5

In column B there are student names. One student may have one or more
records.
In column C are students' grades. I need to find best and worst grade to
corresponding student.
My solution is current:
1) Sort all table (sorting by Name);
2) add new column D: "COUNTIF", which count how similar names is in table.
3) add new column E: "Array", which will help using MAX and MIN functions
in
columns F and G.
4) add new column F: "Worst", where I get worst grade in the table of
corresponding student;
5) add new column G: "Best", where I get best grade in a table of
corresponding student.

Formula in E3:
IF(B3=B2;E2;ADDRESS(ROW();COLUMN(C3);4;1)&":"&ADDR ESS(ROW()+D3-1;COLUMN(C3);4;1))
Formula in F3: MAX(INDIRECT(E3))
Formula in G3: MIN(INDIRECT(E3))

Is there another way to solve this: not using sorting and not using so
many
columns?





--
A.B.