Displaying winner/first four placings.

T

Toobi-Won Kenobi

Using one of the two formulas below to display the name (in column B) of the
highest scorer (from column C) in another cell (B*)
is it possible to modify either to display the highest four placings?
How would I allow for a tied score.

=OFFSET(C1,MATCH(MAX(C1:C10),$C$1:$C$10,0)-1,-1,1,1)
or
=INDEX(B:B,MATCH(MAX(C:C),C:C,0))

Regards
TWK
 
B

BoniM

=OFFSET($C$1,MATCH(LARGE($C$1:$C$10,1),$C$1:$C$10,0)-1,-1,1,1)
or
=INDEX($B:$B,MATCH(LARGE($C:$C,1),$C:$C,0))
Copy down for how many places you wish to display and change second large
argument to desired place.
LARGE($C:$C,2) for second
LARGE($C:$C,3) for third, etc.
These formulas will repeat the first name found for all repeat scores, so
will need to make manual changes in case of a tie.
 
T

Toobi-Won Kenobi

Hello BoniM

Perfect!

Many thanks

(liked your "Night flight to Venus" BTW) <G>

TWK
 
B

BoniM

Nah, it's not perfect, if it were perfect, it would handle ties... :-(
Do you have some data that could be a tie breaker? An overall rank score or
something like that?
If not, you could use this to add a rank column:
=RANK(C1,$C$1:$C$10,1)+COUNTIF($C$1:C1,C1)-1
Copy into D1 and fill down, but it rather arbitrarily gives the better rank
to the first one it comes across.
But then:
=INDEX($B:$B,MATCH(LARGE($D:$D,1),$D:$D,0))
would never give you any duplicates.

boney m? giving your age away? i guess that's as close a match as toobi-won
:)
may the force not be against you!
 
T

Toobi-Won Kenobi

B,

We can live with the tie issue (haven't had one yet anyway)
But will try your suggestion on Monday.
Giving my age away? I had the white suit, black shirt and medallions etc. I
looked like a negative!
The force is always against me, I'm married! (Roobi-won)
Regards

TWK
 
Top