Try:
=MAX((B3:B15="Alice")*(C3:C15))
=MIN(IF(B3:B15="Alice", C3:C15))

both array entered using Cntrl+Shift+Enter (if done properly, XL will put
braces { } around your formula, otherwise you'll likely get incorrect
results). You should then be able to eliminate columns D and E. Also, it
won't be necessary to sort the list.

"Aivis" wrote:


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.