Best/Worst grade

[Aivis]

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)&":"&ADDRESS(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?

 

[T. Valko]

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)

 

[JMB]

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]

Not solved.
I have current result:
B C J K
2 Name Grade2 Worst_a Worst_b
3 Alice 2 4 4
4 Alice 3 4 4
5 Alice 4 4 4
6 David 3 4 4
7 David 2 4 4
8 Joe 2 4 4
9 Joe 3 4 4
10 Joe 1 4 4
11 John 3 4 4
12 Josef 1 4 4
13 Karin 4 4 4
14 Karin 2 4 4
15 Philip 6 4 4

In column "Worst_a" (J2:J14) I used current array formula:
{=MAX(IF(B$3:B$15=B3;C$3:C$15))}
In column "Worst_b" (K2:K14) I used current array formula:
{=MAX((B3:B15=B3)*(C3:C15))}

In all rows there ar MAX value of "Alice"

 

[Bob Phillips]

Not if you array-enter (Ctrl-Shift-Enter) it there aren't, row 6 shows 3 for
Max.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Aivis]

I use array formula (Ctrl + Shift + Enter), but all rows returns MAX value of
"Alice" grades.

 

[Toppers]

Formula in J2:
=MAX(IF(B$2:B$14=B2,C$2:C$14))

Formula in K2:
=MIN(IF(B$2:B$14=B2,C$2:C$14))

Both entred with Ctrl+Shift+Enter (and copied down) gave the following
results which look OK to me:

J K
4 2
4 2
4 2
3 2
3 2
3 1
3 1
3 1
3 3
1 1
4 2
4 2
6 6

 

[Aivis]

Now I get it!
I was select all column and then wrote array formula not enter array formula
and copy down.

 

[Toppers]

Glad it's fixed. Thanks for the feedback.

Now I get it!
I was select all column and then wrote array formula not enter array formula
and copy down.
--
A.B.


"Toppers" rakstîja: