[Ben]

Thanks to Bob I have a formula which ranks my team players in position
order...(see formula below)...
=IF(COUNTIF($D$2:$D$706,D2)1,"T","")&COUNTA($D$2: $D$706)-SUM(IF(D2$D$2:$D$706,1/COUNTIF($D$2:$D$706,$D$2:$D$706)))-1

I now need a formula which calculates how many positions a player has moved
up or down from the previous week ie:

Club Player Name Points Position Pos Gained or Lost
Fremantle SANDILANDS,Aaron 1 (would like an arrow up
or
down here) Up 4
Western Bulldogs CROSS,Daniel T2 down 1
West Coast KERR,Daniel T2 down 2
Western Bulldogs JOHNSON,Brad 3 down 2

[Ben]

I need a formula or macro which determines the position of a player on
a list. The trick is that I want players, who's scores are tied, to be shown
as tied (see example below).

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

I received help from Bob who suggested this:

=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

But I'm finding that this array formula is cumbersome and doesn't work
correctly because it's returning the following numbers:

Pos. Club Player Name Total
611.999999999999 Fremantle SANDILANDS,Aaron 120
T612.999999999999 Western Bulldogs CROSS,Daniel 105
T612.999999999999 West Coast KERR,Daniel 105
613.999999999999 Western Bulldogs JOHNSON,Brad 103

As you can see the formula is sorting the positions correctly however it
starts at 610 and has 12 decimal 9s that I can't get rid of.

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.

Hope someone is able to assist.

Ben


"Ben" wrote:

Thanks to Bob I have a formula which ranks my team players in position
order...(see formula below)...
=IF(COUNTIF($D$2:$D$706,D2)1,"T","")&COUNTA($D$2: $D$706)-SUM(IF(D2$D$2:$D$706,1/COUNTIF($D$2:$D$706,$D$2:$D$706)))-1

I now need a formula which calculates how many positions a player has moved
up or down from the previous week ie:

Club Player Name Points Position Pos Gained or Lost
Fremantle SANDILANDS,Aaron 1 (would like an arrow up
or
down here) Up 4
Western Bulldogs CROSS,Daniel T2 down 1
West Coast KERR,Daniel T2 down 2
Western Bulldogs JOHNSON,Brad 3 down 2