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

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

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

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

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

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

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

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

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)



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

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

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