Leader Board

[Ben]

I would like to have a formula which determines the position of a player on
my list. The trick is that I want players who's scores are tied to be shown
as tied (see example below). Can anyone help please.

Pos. Club Player Name Total
1 Fremantle SANDILANDS,Aaron 120
T2 Western Bulldogs CROSS,Daniel 105
T2 West Coast KERR,Daniel 105
3 Western Bulldogs JOHNSON,Brad 103

 

[Bob Phillips]

=IF(COUNTIF($D$2:$D$5,D2)>1,"T","")&COUNTA($D$2:$D$5)-SUM(IF(D2>$D$2:$D$5,1/COUNTIF($D$2:$D$5,$D$2:$D$5)))-1

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

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

 

[Ben]

Hi Bob,

I copied and pasted the firmula to my sheet and it's given me these numbers

611.999999999999
T612.999999999999
T612.999999999999
613.999999999999

I did control shift enter it...by the way my sheet goes down to row 706.
Would that make a diference?

 

[Bob Phillips]

Try this Ben,

=IF(COUNTIF($D$2:$D$760,D2)>1,"T","")&ROUND(COUNTA($D$2:$D$760)-SUM(IF(D2>$D$2:$D$760,1/COUNTIF($D$2:$D$760,$D$2:$D$760)))-1,0)

array entered it is.

--
---
HTH

Bob

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

 

[Ben]

Thanks Bob...by the way do you have an answer for this...

I'm also looking for a formula which will indicate how far up or down a
player has moved from week to week (similar to the current Masters
Leaderbaord).
Ideally I would like the number to be preceded with either the up or down
arrow if possible.

 

[Bob Phillips]

Assuming that one week is in say column C, the next is in D, then you can
get the indicator woth

=SUBSTITUTE("D"&SUBSTITUTE(C2,"T","")-SUBSTITUTE(D2,"T",""),"D-","U")

not arrows, but U and D.

--
---
HTH

Bob

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