Ranking search
 

Dear All,

I wan a formula to "detect" who have the highest mark amount the whole
score table.

Test 1 Test 2
Jo 56 55
Mark 98 06
Simon 89 57
Stella 88 58
Jean 50 51

I wan a Formula to show:

Highest sco Mark, Test 1
Lowest sco Mark, Test 2

Ranking search
 

Assumptions:

A2:A6 contains the name

B1:C1 contains the column header/label

B2:C6 contains the data

Formula:

Let E2 contain 'Highest Sco' (just a label)

F2:

=INDEX(A2:A6,MIN(IF(B2:C6=MAX(B2:C6),ROW(B2:C6)-ROW(B2)+1)))&",
"&INDEX(B1:C1,MATCH(MAX(B2:C6),INDEX(B2:C6,MIN(IF( B2:C6=MAX(B2:C6),ROW(B2
:C6)-ROW(B2)+1)),0),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. For the lowest
score, replace MAX with MIN.

Hope this helps!

In article ,
Will wrote:

Dear All,

I wan a formula to "detect" who have the highest mark amount the whole
score table.

Test 1 Test 2
Jo 56 55
Mark 98 06
Simon 89 57
Stella 88 58
Jean 50 51

I wan a Formula to show:

Highest sco Mark, Test 1
Lowest sco Mark, Test 2

Ranking search
 

This will show the first person with the highest score in test1:
=index($a$2:$a$999,match(max($b$2:$b$999),$b$2:$b$ 999,0))

First person with the lowest score in test1:
=index($a$2:$a$999,match(min($b$2:$b$999),$b$2:$b$ 999,0))

Change the range to match (I went through row 999) and change the column
reference for the other tests.




Will wrote:

Dear All,

I wan a formula to "detect" who have the highest mark amount the whole
score table.

Test 1 Test 2
Jo 56 55
Mark 98 06
Simon 89 57
Stella 88 58
Jean 50 51

I wan a Formula to show:

Highest sco Mark, Test 1
Lowest sco Mark, Test 2

--

Dave Peterson

Ranking search
 

Ties?

Biff

"Domenic" wrote in message
...
Assumptions:

A2:A6 contains the name

B1:C1 contains the column header/label

B2:C6 contains the data

Formula:

Let E2 contain 'Highest Sco' (just a label)

F2:

=INDEX(A2:A6,MIN(IF(B2:C6=MAX(B2:C6),ROW(B2:C6)-ROW(B2)+1)))&",
"&INDEX(B1:C1,MATCH(MAX(B2:C6),INDEX(B2:C6,MIN(IF( B2:C6=MAX(B2:C6),ROW(B2
:C6)-ROW(B2)+1)),0),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. For the lowest
score, replace MAX with MIN.

Hope this helps!

In article ,
Will wrote:

Dear All,

I wan a formula to "detect" who have the highest mark amount the whole
score table.

Test 1 Test 2
Jo 56 55
Mark 98 06
Simon 89 57
Stella 88 58
Jean 50 51

I wan a Formula to show:

Highest sco Mark, Test 1
Lowest sco Mark, Test 2

Ranking search
 

In article ,
"T. Valko" wrote:

Ties?

Biff

A different approach would be necessary...

Ranking search
 

This OP posted a similar question last night but didn't explain it very
well. (at least, to where I could understand what they wanted)

Biff

"Domenic" wrote in message
...
In article ,
"T. Valko" wrote:

Ties?

Biff

A different approach would be necessary...

Ranking search
 

Yeah, I noticed it after posting my solution. It seems that half the
battle is trying to understand the question... :)

In article ,
"T. Valko" wrote:

This OP posted a similar question last night but didn't explain it very
well. (at least, to where I could understand what they wanted)

Biff

Ranking search
 

Domenic,

Formula works. If I would like to ranking them accordingly to 1st, 2nd &
3rd. How would I go abt?

"Domenic" wrote:

Yeah, I noticed it after posting my solution. It seems that half the
battle is trying to understand the question... :)

In article ,
"T. Valko" wrote:

This OP posted a similar question last night but didn't explain it very
well. (at least, to where I could understand what they wanted)

Biff