Having solved my last skins query (thank you)
I now have this one.
I've adapted the spreadsheet I use to record stableford scores for the golf society I run.
4 of us are off to Spain next week and we're going to have a few rounds of skins, I'd like to be able to record these automatically without having to manually calculating scores.
For those of you not familiar with the skins format, each hole has a 'skin', to win the skin, a player must have the lowest unique net score for that hole between those playing.
If more than one player has the lowest unique score, then the skin is rolled over to the next hole and is worth 2 skins. This continues? until one player has the unique lowest score.
At the end of the round, the winner is the player with the most 'skins'
I have added some sample scores from the last time we played for greater detail and context.
What I want to do, is have a function/ formula that will calculate and display the results as they are in the green shaded cells on lines 34 and 35.
I've tried several formulas but always seem to come unstuck.
Thanks in advance for any advice
I now have this one.
I've adapted the spreadsheet I use to record stableford scores for the golf society I run.
4 of us are off to Spain next week and we're going to have a few rounds of skins, I'd like to be able to record these automatically without having to manually calculating scores.
For those of you not familiar with the skins format, each hole has a 'skin', to win the skin, a player must have the lowest unique net score for that hole between those playing.
If more than one player has the lowest unique score, then the skin is rolled over to the next hole and is worth 2 skins. This continues? until one player has the unique lowest score.
At the end of the round, the winner is the player with the most 'skins'
I have added some sample scores from the last time we played for greater detail and context.
What I want to do, is have a function/ formula that will calculate and display the results as they are in the green shaded cells on lines 34 and 35.
I've tried several formulas but always seem to come unstuck.
Thanks in advance for any advice
| Skins.xlsx | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
| 4 | HOLE | ||||||||||||||||||||||
| 5 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | TOTAL | ||||
| 6 | DATE | METRES | 286 | 252 | 119 | 279 | 461 | 325 | 125 | 338 | 424 | 460 | 164 | 314 | 368 | 281 | 333 | 435 | 101 | 336 | 5401 | ||
| 7 | YARDS | 313 | 276 | 130 | 305 | 504 | 355 | 137 | 370 | 464 | 503 | 179 | 343 | 402 | 307 | 364 | 476 | 110 | 367 | 5907 | |||
| 8 | PAR | 4 | 4 | 3 | 4 | 5 | 4 | 3 | 4 | 5 | 5 | 3 | 4 | 4 | 4 | 4 | 5 | 3 | 4 | 72 | |||
| 9 | S.I. | 12 | 16 | 18 | 2 | 10 | 4 | 14 | 8 | 6 | 1 | 13 | 3 | 5 | 17 | 11 | 7 | 15 | 9 | ||||
| 10 | PLAYER | Handicap | |||||||||||||||||||||
| 11 | DC | 3 | 6 | 5 | 4 | 6 | 6 | 6 | 7 | 5 | 5 | 5 | 6 | 4 | 5 | 4 | 4 | 4 | 3 | 5 | 90 | ||
| 12 | JD | 1 | 4 | 4 | 4 | 7 | 6 | 5 | 7 | 5 | 6 | 7 | 4 | 6 | 5 | 6 | 5 | 4 | 6 | 4 | 95 | ||
| 13 | SH | 2 | 5 | 5 | 5 | 5 | 5 | 6 | 6 | 6 | 7 | 7 | 7 | 5 | 5 | 5 | 5 | 6 | 3 | 4 | 97 | ||
| 14 | SP | 0 | 4 | 4 | 4 | 5 | 5 | 5 | 5 | 6 | 6 | 6 | 6 | 7 | 7 | 7 | 7 | 4 | 4 | 4 | 96 | ||
| 15 | |||||||||||||||||||||||
| 16 | Shots | ||||||||||||||||||||||
| 17 | DC | -15 | 1 | 1 | 1 | 3 | |||||||||||||||||
| 18 | JD | -17 | 1 | 1 | |||||||||||||||||||
| 19 | SH | -16 | 1 | 1 | 2 | ||||||||||||||||||
| 20 | SP | -18 | 0 | ||||||||||||||||||||
| 21 | |||||||||||||||||||||||
| 22 | Net Score | ||||||||||||||||||||||
| 23 | DC | 6 | 5 | 4 | 5 | 6 | 6 | 7 | 5 | 5 | 4 | 6 | 3 | 5 | 4 | 4 | 4 | 3 | 5 | 87 | |||
| 24 | JD | 4 | 4 | 4 | 7 | 6 | 5 | 7 | 5 | 6 | 6 | 4 | 6 | 5 | 6 | 5 | 4 | 6 | 4 | 94 | |||
| 25 | SH | 5 | 5 | 5 | 4 | 5 | 6 | 6 | 6 | 7 | 6 | 7 | 5 | 5 | 5 | 5 | 6 | 3 | 4 | 95 | |||
| 26 | SP | 4 | 4 | 4 | 5 | 5 | 5 | 5 | 6 | 6 | 6 | 6 | 7 | 7 | 7 | 7 | 4 | 4 | 4 | 96 | |||
| 27 | |||||||||||||||||||||||
| 28 | Lowest Score | 4 | 4 | 4 | 4 | 5 | 5 | 5 | 5 | 5 | 4 | 4 | 3 | 5 | 4 | 4 | 4 | 3 | 4 | ||||
| 29 | No Of Times | 2 | 2 | 3 | 1 | 2 | 2 | 1 | 2 | 1 | 1 | 1 | 1 | 3 | 1 | 1 | 3 | 2 | 3 | ||||
| 30 | |||||||||||||||||||||||
| 31 | Won Y/N | N | N | N | Y | N | N | Y | N | Y | Y | Y | Y | N | Y | Y | N | N | N | ||||
| 32 | Skin | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||
| 33 | |||||||||||||||||||||||
| 34 | Winner | SH | SP | DC | DC | JD | DC | DC | DC | ||||||||||||||
| 35 | Number Won | 4 | 3 | 2 | 1 | 1 | 1 | 2 | 1 | ||||||||||||||
Skins | |||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C6:T6 | C6 | =IF($G$1=$A$107,C101,IF($G$1=$A$108,C106,IF($G$1=$A$109,C111))) |
| U6:U8,U23:U26,U17:U20,U11:U14 | U6 | =SUM(C6:T6) |
| C7:T7 | C7 | =C6*1.09361 |
| C8:T9 | C8 | =IF($G$1=$A$107,C102,IF($G$1=$A$108,C107,IF($G$1=$A$109,C112))) |
| A17:A20 | A17 | =A11 |
| B17:B20 | B17 | =B11-18 |
| C17:T20 | C17 | =INDEX($Y$5:$AQ$9,MATCH($B11,$Y$5:$Y$9),MATCH(C$9,$Y$5:$AQ$5)) |
| C23:T26 | C23 | =C11-C17 |
| A23:A26 | A23 | =A11 |
| C28:T28 | C28 | =MIN(C23:C26) |
| C29:T29 | C29 | =COUNTIF(C23:C26,C28) |
| C31:T31 | C31 | =IF(C29=1, "Y","N") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| C11:T14 | Cell Value | =C$8-1 | text | YES |
| C11:T14 | Cell Value | =C$8 | text | YES |
| C11:T14 | Cell Value | =C$8+1 | text | YES |
