• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Office Golf Bet - spreadsheet challenge

S

spena129

New Member
Hi All,

I have a little office bet going with a couple of my colleagues centered around the PGA Championship currently being played.

The bet: Pick 5 players in the field as your "team". At the end of the tournament, add the total winnings of each player on your team to find the winner of the bet.

Naturally, I opened Excel and began designing a sort of tracker for the tournament. As I am building this thing, I thought to myself, "I wonder how the folks on the Chandoo.com forum would create the formulas to accomplish this?"

A challenge: My goal is to drop in the results data from the tournament (pga.com) into the spreadsheet and it will automatically calculate the projected winnings (and bet winner) after each round, and the final results when all is finished.

A couple wrinkles: It would be pretty easy if everyone finished 1,2,3,etc., but there inevitably are ties. When this happens, the money is divided evenly among the players in the tie (i.e. 4 players tie for 2nd, then 2nd, 3rd, 4th, and 5th place add winnings together and split 4 ways). Is there a way to evaluate this situation and give the desired result? Also, I was hoping to calculate the place of each player chosen in the team in each round, regardless how far into the tournament we are (i.e. still calculates player 1's place in round 1 as 10th, even though the data suggests his current place is tied 25th).

I'll be working on it over the next couple of days (hopefully to create a template for future bets), but would appreciate some help! Download the workbook using the link below:

https://dl.dropbox.com/u/97042046/Golf%20Tournament_Bet.xlsx


Thanks in advance!
 
Hi Steve ,


An interesting problem. Can you explain things in a little more detail ?


1. Is the data in columns L through Y going to come completely from the website ? Or is any of it to be calculated from the scores ?


2. Is the data in columns B through E to be arrived at by matching the names in column F against the master data in columns N through V ?


3. Are you looking for the calculated values in columns G through I ?


Can you please take a sample , where the tournament has been completed , and the bettors have been given their due based on the correctness or otherwise of their picks ?


Narayan
 
Hi Narayank991,

Answers to your questions.


1) The purse for the tournament will always come from the web. No calculations on that, but I guess the only other data you would need to collect from the web would be the actual scores of the players (Columns R through U). You could then calculate the place (Column L) based on scores.

2) YES. Their place should be matched from the results section.

3) YES. Columns G through I will take each players "place" from each round (columns B through E), count how many players (if any) are in the same group who are tied, and calculate the winnings (sum of winnings/#players tied for position). For example, lets say one of my players is T45 along with 17 other players. His winnings would be the sum of prize $ of places 45-62 divided by 18.


I'll work on completing a past tournament shortly and post the link for download.


Thanks again!
 
Took a lot longer than expected manually (which makes me even more excited to figure out how to automate this), but here is a completed sample: https://dl.dropbox.com/u/97042046/Golf%20Tournament_Bet_completed.xlsx


I had to add some helper columns to calculate their total score after the 2nd and 3rd rounds to determine their places for use in projecting $ (by converting the data to a table and sorting after round 1, round 2, and round 3).


Easy Friday at work for me, so I'll be racking my brain all afternoon!
 
Hi Steve ,


Can you download the file from here and check the section for LENNY ? Are the results correct ?


https://docs.google.com/open?id=0B0KMpuzr3MTVYVRxS0o5SDRPSkk


Narayan
 
Incredible, Narayank! This is correct. I was working all afternoon yesterday on the winnings portion. It looks like you were able to rank the players and add "T" afterwards (if applicable)? I am no excel ninja, so I was trying to create an additional column for each rounds' rankings and use countif to count all occurrences of a ranking, then use offset to create the range of winnings (in the "purse" section)and divide by rank occurrence, but could never get it right. I see your use of array formulas, and I must say that I don't quite understand when I should use them or how.

Again, I thank you for helping. Brilliant work!
 
Good afternoon Steve


The taking of bets on sports fixtures is an acceptable form of relaxation and a hobbie so long as one does not bet the salary,(one hell of an angry partner). Bearing in mind the word "sport" in the above what are you doing betting on Golf, there is a contraindication in terms, I have run every IF, ELSE, OFFSET VLOOKUP, HLOOKUP, MATCH and just about every other function in the Excel library,and I can not get Excel to make Golf=Sport
 
Hi Narayan..

Congrats.. Half Century.. for the 4th Place Formula.. Evaluate Formula.. took 50+ Click...

What a marvelous performance, Good Shot..

Regards,

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