We've launched the new forums! Read more here

Excel Help? Run Skins Game Easily in Excel

ddxu

ddxu

New member
Joined
Dec 5, 2010
Messages
9,843
Reaction score
34
Location
CVG
Handicap
12.6
I have maxed out my (limited) Excel skills and I'm hoping someone can help me and hopefully others as well. I am looking to optimize an Excel spreadsheet to easily run a skins game for a 40 person outing my father in law runs.

Goal: Highlight a given cell when that cell wins a skin on a given hole

Current scenario. I have 40 golfer names on 40 different rows. I have holes 1-18 each in their own column. I am using Excel's 'Conditional Formatting' to highlight a cell if it is a unique record for that hole. Problem I am seeing, it also highlights unique BAD scores. So if a golfer has a birdie and another golfer has a quad, both are highlighted because they are unique.

Basically, I want to Highlight a given cell, If that record is unique AND <= 5.

I just sat through a manual reading & checking for skins and i KNOW there must be a simpler way to do this.
 
If your on Excel 2010, there is a rule for that. Select the column you want then under More rule then specify Bottom 1 and apply the formatting you want. I tested it real quick and it work for me.
 
lets pretend ur data set only goes to Column F for example's sake

make another column in and put in =min($a1:$f1) and drag this all the way down <-- this will give you the minimum score of rows
make another column next to that and put in =countif($a1:$f1,$XY) <-- X is column of the cell you did the min in, Y is row you put the 1st min in so if your min cell is AQ1 put $AQ1 where i put in $XY

make a 3rd column the the right of the other two, and put in =IF(AQ2=1,AO1,"") this forces you to only pick unique lowest numbers
Set your conditional formula to highlight only this cell (AP3 in my example)

Alternatively you can send to me and i can do it for you PM me and i'll send u my email.

Cliffs:
Set formula to find min
Set formula to count how many times it occurs
Set unique identifier for conditional formatting to lookup against
 
Last edited:
lcsmrtn said:
If your on Excel 2010, there is a rule for that. Select the column you want then under More rule then specify Bottom 1 and apply the formatting you want. I tested it real quick and it work for me.
Click to expand...

This method will i.d. the lowest score, but shows the lowest score no matter what. So it will show all the 4's if the lowest score is a 4, but I hope to see nothing highlighted (since the 4's cancel each other out of a skin). I appreciate the help though, I know I'm not the only one who could use this to make skins easier to manage.
 
dubbledxu said:
This method will i.d. the lowest score, but shows the lowest score no matter what. So it will show all the 4's if the lowest score is a 4, but I hope to see nothing highlighted (since the 4's cancel each other out of a skin). I appreciate the help though, I know I'm not the only one who could use this to make skins easier to manage.
Click to expand...

try out my way.. i know my example is kind of hard to understand, but it first identifies lowest number, then checks it for duplicates, and THEN uses the conditional formatting to highlight it if there's no duplicates.

if u send me ur xls file i can get it back to u in like 5 min.
 
InitialDL said:
try out my way.. i know my example is kind of hard to understand, but it first identifies lowest number, then checks it for duplicates, and THEN uses the conditional formatting to highlight it if there's no duplicates.

if u send me ur xls file i can get it back to u in like 5 min.
Click to expand...

I plan on trying it out tmrw while at work. I really appreciate the help and the offer to PM you if I cant figure it out.
 
dubbledxu said:
This method will i.d. the lowest score, but shows the lowest score no matter what. So it will show all the 4's if the lowest score is a 4, but I hope to see nothing highlighted (since the 4's cancel each other out of a skin). I appreciate the help though, I know I'm not the only one who could use this to make skins easier to manage.
Click to expand...


No problem, looks like DL's skills are much more advanced than mine! All the better!
 
haha ok

just noticed i pinned some columns wrong.. but i fixed it just now.. gluck!
 
lcsmrtn said:
No problem, looks like DL's skills are much more advanced than mine! All the better!
Click to expand...

I am an excel monkey aka analyst by trade. :blob:
 
Perfect, by the grace of God, I was able to get this working. I surprised myself and couldnt copy your formulas exactly, since my sample player names run a2-a21 with the hole scores running from B-T. I have the formula cells in a few rows beneath the players (B24, B25, B26).

Now I'm going to get picky, once I get a skin identified (as seen by a score value in the IF field (my B26 your AP3)), is there a way to highlight the actual cell of the score itself (say for examples sake B9)? This way, my eye will go right to that cell instead of having to search the column for it? I know this isnt a big deal if you run a small skins game, but we have 44 golfers and hope to not have to search for it.
 
dubbledxu said:
Perfect, by the grace of God, I was able to get this working. I surprised myself and couldnt copy your formulas exactly, since my sample player names run a2-a21 with the hole scores running from B-T. I have the formula cells in a few rows beneath the players (B24, B25, B26).

Now I'm going to get picky, once I get a skin identified (as seen by a score value in the IF field (my B26 your AP3)), is there a way to highlight the actual cell of the score itself (say for examples sake B9)? This way, my eye will go right to that cell instead of having to search the column for it? I know this isnt a big deal if you run a small skins game, but we have 44 golfers and hope to not have to search for it.
Click to expand...

ok so now set conditional formatting.

for column B, highlight B1 to B23, and set condition to =B$26

highlight B1 to B23 again, click copy format (paintbrush icon) and highlight C1 to T23. Hit enter

This will make the column highlight the value you have set for your IF formula in B26
 
I have that conditional formatting set, but it is highlighting the entire column instead of just the skin cell. What cell is AO1 referencing in your IF statement? =IF(AQ2=1,AO1,"")

my players run b2:b21 my min cell is b24, countif cell b25, if cell b26.
 
I didnt make one, LOL i was just working off what i think yours looks like.

If your MIN says =min(B$2:B$21)
your count if should say =countif(B$2:B$21,B$24)
your IF cell should say =IF(B$25=1,B$24,"")

Conditional formatting should reference starting @ B26
 
InitialDL and I traded a few emails and got it sorted out. Turns out Windows and Mac versions of Office have different functions for conditional logic. Regardless, he was able to point me into the right direction. I know this may be useful for some, so I uploaded the template to share.

http://goo.gl/00VM8

Using this document, you can easily identify skin winners for an outing. I hope this helps, thanks again for all the help.
 
Glad to help! :drinks:
 
DDXU: My apologies for the premature request. Appears I was using an outdated version of IE and that is what was preventing me from downloading the Excel file. I copied the link into Chrome and it worked fine.

InitialDL: Question - Is there a way to add formatting to account for handicaps so that this template could calculate net skins in addition to Gross?
 
jasbday35 said:
Can this be modified to have 50 players instead of 20?
Click to expand...

jasbday35 said:
How would I adjust the worksheet to include 50 payers?
Click to expand...

Without checking I would say the easiest way would be to insert however many rows (or columns depending on which way the sheet is initially set up) you need in the middle of the rows where the player names are as the conditional formatting and formula should automatically recalculate based on the number of inserted rows
 
I didn't look at the initial solution, but did a mock up of how I'd do it and took all of 5 minutes and would work for any number of players. If anyone needs help, feel free to PM and I'd be happy to put my excel skills to use.
 
Struggling with skin game formula here.

Skin game is 1/2 stroke for all holes where you get a stroke,
we are reducing halves of holes.

1/2 STROKE SKINS GAME PROGRAM
Player
Hole # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Hole Hndcp 8 12 10 2 14 16 4 18 6 5 1 9 7 17 3 15 13 11
Player
Hndcp
A 0 4 2 5 4 4 4 4 3 4 4 6 4 4 3 5 3 3 4
B 5 4 4 5 5/4.5 4 5 5/4.5 3 4 5/4.5 6/5.5 5 4 3 5/4.5 5 3 4
C 12 4/3.5 3/2.5 6/5.5 6/5.5 6 4 5/4.5 3 5/4.5 4 6/5.5 4/3.5 6/5.5 6 6/5.5 5/4.5 6 5/4.5
D 20 5/4.5 5/4.5 6/5.5 5/3.5 7/6.5 5/4.5 6/5.5 4/3.5 5/4.5 7/6.5 7/5.5 7/6.5 7/6.5 6/5.5 7/6.5 7/6.5 5/4.5 6/5.5


All Hole Handicap # (row 4) that is less than or equal to the players handicap, (column B)
results in minus .5 from score in that appropriate Hole #/Player cell.
Highlight lowest score in red



Hole # 1 Player C makes a 4 net 3.5 on #1, since no one made a birdie, his 3.5 wins a skin
Hole # 2 Player A makes birdie on # 2, player C gets a 1/2 stroke but Player A wins the skin
Hole # 4 Player D gets 1.5 strokes on # 4, no one makes a birdie, so his 3.5 wins a skin
Hole # 12 Player C makes a 4 net 3.5 on # 12, no one makes a birdie, again his 3.5 wins a skin
Hole # 15 Player B make s a 5 net 4.5 on # 15, no one makes a birdie, his 4.5 wins a skin
Hole # 16 Player A makes a birdie on # 16 and wins a skin


its a lot to consume, hope my example chart above is clear, obviously cut/paste didn't come thru perfectly, Hole # and Hole hndcp did not align

thanks
 
You must log in or register to reply here.
Back
Top