|
Leader Board
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 |
Leader Board
=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 |
Leader Board
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 |
Leader Board
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 |
Leader Board
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 |
Leader Board
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 |
| All times are GMT +1. The time now is 01:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com