#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Best/Worst grade


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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Best/Worst grade

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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Best/Worst grade

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Best/Worst grade

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"

--
A.B.


"JMB" rakstîja:

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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Best/Worst grade

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" wrote in message
...
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"

--
A.B.


"JMB" rakstîja:

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.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Best/Worst grade

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

--
A.B.


"Bob Phillips" rakstîja:

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" wrote in message
...
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"

--
A.B.


"JMB" rakstîja:

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.




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default Best/Worst grade

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" wrote:

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

--
A.B.


"Bob Phillips" rakstîja:

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" wrote in message
...
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"

--
A.B.


"JMB" rakstîja:

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.




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Best/Worst grade

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:

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" wrote:

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

--
A.B.


"Bob Phillips" rakstîja:

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" wrote in message
...
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"

--
A.B.


"JMB" rakstîja:

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.



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default Best/Worst grade

Glad it's fixed. Thanks for the feedback.

"Aivis" wrote:

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:

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" wrote:

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

--
A.B.


"Bob Phillips" rakstîja:

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" wrote in message
...
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"

--
A.B.


"JMB" rakstîja:

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find best and worst grade? Aivis Excel Worksheet Functions 5 August 6th 07 07:04 PM
when doing a grade book how do you drop the lowest grade dove Excel Worksheet Functions 1 November 28th 06 07:54 PM
when doing a grade book how do you drop the lowest grade CLR Excel Worksheet Functions 0 November 28th 06 07:53 PM
The worst Code Ever Jacob_F_Roecker Excel Discussion (Misc queries) 4 March 13th 06 12:49 AM
Grade Percentage into letter grade James Excel Discussion (Misc queries) 4 December 14th 05 04:24 AM


All times are GMT +1. The time now is 10:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"