ffrllc
asked on
Creating spreadsheet to split prize money at the end of a poker tournament.
Hello all,
I'm using Excel 2002 and need to create a spreadsheet for figuring a way to evenly split prize money at the end of a poker tournament (based on the size of each players chip stack). Let’s say there are 5 players left (they all have different amounts of chips left in their stack). They talk about a split, but the guys with the bigger stacks should get more in this split because they have a better chance of winning more prize money based on their lead in chips. There is a way to calculate what each player’s “equity” is based on the amount of chips they have.
I need to have this spreadsheet automatically figure as much as possible as I will only have time (when I'm at the end of a tournament and will need to use it) to enter the very minimum inputs.
Here is how I would like it to perform the calculations:
The standard deal is to look at how much money is still at stake -- in other words, assign everyone left in the field the lowest remaining prize amount and see what's left over. Then distibute those funds proportionately according to the chip stacks. Simple example:
Three players are left with stacks of 200k, 170k, and 130k (500k total).
The prizes are $1000/$700/$500. Each player gets at least $500, so there's
only $700 in play (1000+700+500-3*500). So the deal amounts would be
Player 1: 200k/500k = 40%, so $500 + 40% * $700 = $780
Player 2: 170k/500k = 34%, so $500 + 34% * $700 = $738
Player 3: 130k/500k = 26%, so $500 + 26% * $700 = $682
Now, this is just one example. The spreadsheet will need to accommodate different amounts of players involved in the split (sometimes it will be 2 players and it could be up to a max of 9 players).
I really want to have the spreadsheet figure all this by me ONLY entering the remaining players, their chip stack, and the remaining prizes left to be paid.
So in other words, if I enter 4 players, their chips stack, and the 4 remaining prize amounts left to be paid, the spreadsheet will then need to figure the following (obviously in what ever order would work best):
1.) The lowest prize amount that I listed is what everyone is already guaranteed (and remove that from the amount to be split up based on the stack sizes)
2.) Calculate what % of chips in play each player has
3.) Multiply that % of each player’s chips to the remaining prize pool to be split.
4.) Add the amount each player is guaranteed back to that player (4th place money from this example) along with his split amount to come up with each player’s total share based on his stack size.
5.) Even though there will be 9 spots (as there could be up to 9 players involved), the spreadsheet should only perform these calculations for the amount of players I enter (4 players in this example).
I have a spreadsheet started that I could send anyone interested in helping. I'm sure seeing this spreadsheet will make it much easier to understand what I'm trying to do.
Thanks in advance for any help...
I'm using Excel 2002 and need to create a spreadsheet for figuring a way to evenly split prize money at the end of a poker tournament (based on the size of each players chip stack). Let’s say there are 5 players left (they all have different amounts of chips left in their stack). They talk about a split, but the guys with the bigger stacks should get more in this split because they have a better chance of winning more prize money based on their lead in chips. There is a way to calculate what each player’s “equity” is based on the amount of chips they have.
I need to have this spreadsheet automatically figure as much as possible as I will only have time (when I'm at the end of a tournament and will need to use it) to enter the very minimum inputs.
Here is how I would like it to perform the calculations:
The standard deal is to look at how much money is still at stake -- in other words, assign everyone left in the field the lowest remaining prize amount and see what's left over. Then distibute those funds proportionately according to the chip stacks. Simple example:
Three players are left with stacks of 200k, 170k, and 130k (500k total).
The prizes are $1000/$700/$500. Each player gets at least $500, so there's
only $700 in play (1000+700+500-3*500). So the deal amounts would be
Player 1: 200k/500k = 40%, so $500 + 40% * $700 = $780
Player 2: 170k/500k = 34%, so $500 + 34% * $700 = $738
Player 3: 130k/500k = 26%, so $500 + 26% * $700 = $682
Now, this is just one example. The spreadsheet will need to accommodate different amounts of players involved in the split (sometimes it will be 2 players and it could be up to a max of 9 players).
I really want to have the spreadsheet figure all this by me ONLY entering the remaining players, their chip stack, and the remaining prizes left to be paid.
So in other words, if I enter 4 players, their chips stack, and the 4 remaining prize amounts left to be paid, the spreadsheet will then need to figure the following (obviously in what ever order would work best):
1.) The lowest prize amount that I listed is what everyone is already guaranteed (and remove that from the amount to be split up based on the stack sizes)
2.) Calculate what % of chips in play each player has
3.) Multiply that % of each player’s chips to the remaining prize pool to be split.
4.) Add the amount each player is guaranteed back to that player (4th place money from this example) along with his split amount to come up with each player’s total share based on his stack size.
5.) Even though there will be 9 spots (as there could be up to 9 players involved), the spreadsheet should only perform these calculations for the amount of players I enter (4 players in this example).
I have a spreadsheet started that I could send anyone interested in helping. I'm sure seeing this spreadsheet will make it much easier to understand what I'm trying to do.
Thanks in advance for any help...
To save me posting a workbook, here are the formulas you need, and there aren't that many.
cell H5
total amount to be split
=SUMIF(G11:G19,">""",E11:E
cell H6
guaranteed to each
=INDEX(E11:E19,COUNTA(G11:
cell K11
Split Amount (first cell under title)
=H11/SUM($H$11:$H$19)*$H$5
Copy K11 to range K11:K19
The prizes should always be in cells E11:E19, even the amounts paid out to the nineth finish, eighth finish, etc, since the formulas will consider only prizes for which a name appears in column G.
I copied / pasted your template and got the cell references above because MTT pasted into cell D5.
Tom
cell H5
total amount to be split
=SUMIF(G11:G19,">""",E11:E
cell H6
guaranteed to each
=INDEX(E11:E19,COUNTA(G11:
cell K11
Split Amount (first cell under title)
=H11/SUM($H$11:$H$19)*$H$5
Copy K11 to range K11:K19
The prizes should always be in cells E11:E19, even the amounts paid out to the nineth finish, eighth finish, etc, since the formulas will consider only prizes for which a name appears in column G.
I copied / pasted your template and got the cell references above because MTT pasted into cell D5.
Tom
I should also have included this note: the remaining player names go into column G and enter player's chip stack in column I. For any places that are without chips, (9th, 8th, etc) the cells in columns G and I must be blank.
ASKER
How can I have the cells in K11-K19 show blank unless something is entered into the corresponding H column? So if H15 has something entered in it, then the function in K15 would be performed. If H15 is left blank, K15 should also be blank. Right now each cell in K11-K19 is performing the function within that cell (whether or not anything is entered in the same H cell). So the K cells where there is nothing entered into the H cell shows up whatever is entered in G6 (as G6 is added on to the results of the formula and the results of the formula if nothing is entered into the corresponding H cell would be 0...so in essence the K cells without corresponding H cells now show 0+G6).
Along the same lines:
Is there also a way to have the player names listed in G11-G19 show in J11-J19 without showing 0 for cells without corresponding J entries? For example, right now, I simply have =J15 in K15. But if nothing is entered into J15, K15 shows 0...not blank. How can I have it leave that cell blank?
Thanks.
Along the same lines:
Is there also a way to have the player names listed in G11-G19 show in J11-J19 without showing 0 for cells without corresponding J entries? For example, right now, I simply have =J15 in K15. But if nothing is entered into J15, K15 shows 0...not blank. How can I have it leave that cell blank?
Thanks.
you confused me! :) I think you just want to hide the zeros from J and K columns.
However, you said,
"so in essence the K cells without corresponding H cells now show 0+G6)." (I think G6 here should be H6)
I read that to mean "the K cells with a name in G but no chips in H show the minimum from H6, but when no name is in G, K still shows $0". That is correct. As I stated, while you can and should have the prize values all the way down to 9th, you can't put the placing playernames below the place that participates in the split.
Now, to hide the zeros, use for cell K11
=IF(ISBLANK(G11),"",H11/SU
copy / paste to range K11:K19
for cell J11
=if(isblank(G11),"",G11)
Anything else, just ask.
Tom
However, you said,
"so in essence the K cells without corresponding H cells now show 0+G6)." (I think G6 here should be H6)
I read that to mean "the K cells with a name in G but no chips in H show the minimum from H6, but when no name is in G, K still shows $0". That is correct. As I stated, while you can and should have the prize values all the way down to 9th, you can't put the placing playernames below the place that participates in the split.
Now, to hide the zeros, use for cell K11
=IF(ISBLANK(G11),"",H11/SU
copy / paste to range K11:K19
for cell J11
=if(isblank(G11),"",G11)
Anything else, just ask.
Tom
ASKER
After I paste that forumla into K11 I get the resulting K11 cell to be blank if nothing is entered into the corresponding G cell (this is where the player name is entered). When I enter a name in that G cell I get a #VALUE! error in the K cell.
But the K cell should show the results when there is an entry in the H cell (that is where the players chip amounts are entered).
The resulting J column should relate to the G column and only show results if a player's name is entered into the G column.
Sorry if I'm making this confusing. I could send you the template so it would be easier to follow/explain.
But the K cell should show the results when there is an entry in the H cell (that is where the players chip amounts are entered).
The resulting J column should relate to the G column and only show results if a player's name is entered into the G column.
Sorry if I'm making this confusing. I could send you the template so it would be easier to follow/explain.
ASKER
When I enter the forumal you mentioned into J11, I get the exact listed text of the forumla to show in J11.
We are making progress. I have a test to check and I will get back to you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I changed the formula in G6 to =MIN(E11:E19) and I changed G7 to =COUNT(E11:E19).
Everything looked good after that, but when I changed where I inputed the players and their chips in column G&H things went wrong.
I moved the same players and same chips in the example from G11,G12,G13 (players) and H11,H12,H13 (their chips) down to the rows 16,17 & 18 I don't get the same results.
The results I get should still be the same $780 (in K16), $738 (in K17), and $682 (in K18) . BUT I don't, I get ($100 in red) (in K16) , ($10 in red) (in K17), and $100 in (K18).
Everything looked good after that, but when I changed where I inputed the players and their chips in column G&H things went wrong.
I moved the same players and same chips in the example from G11,G12,G13 (players) and H11,H12,H13 (their chips) down to the rows 16,17 & 18 I don't get the same results.
The results I get should still be the same $780 (in K16), $738 (in K17), and $682 (in K18) . BUT I don't, I get ($100 in red) (in K16) , ($10 in red) (in K17), and $100 in (K18).
I don't follow why your changes were necessary.
G6.
If you erase Prize breakdown amounts from column E your formula will return the same result as mine. Mine does not depend on some of the prize amounts being blank.
G7
Your formula, again, depends on prize breakdown cells being blank. Mine did not, only the yellow cells which are supposed to be changed, effected the formula.
You can't have blank lines in the yellow zone ABOVE entries. This does not make sense. If the 1st prize is $500, then the prize breakdown needs to be shifted up so $500 appears on row 11. If the reason you copied the players to rows 16 thru 18 was to demo a poker tournament where 1st prize was $100, then that functionality is way beyond the scope of what this little sheet will do.
I think if you familiarize yourself with the sheet as I posted it, you might see it works well enough as is. Among other things, I turned on the "amount held out for 1st" cell, so now it will award 1st place any amount in that cell before it splits the remainder of the prize pool.
On my version, if you put all nine finishers in column G, and zero for the chips for anyone who is out, then the sheet acts as a "standings" report. If you don't want that then leave only players participating in the split in the yellow rows, but don't edit the green rows.
G6.
If you erase Prize breakdown amounts from column E your formula will return the same result as mine. Mine does not depend on some of the prize amounts being blank.
G7
Your formula, again, depends on prize breakdown cells being blank. Mine did not, only the yellow cells which are supposed to be changed, effected the formula.
You can't have blank lines in the yellow zone ABOVE entries. This does not make sense. If the 1st prize is $500, then the prize breakdown needs to be shifted up so $500 appears on row 11. If the reason you copied the players to rows 16 thru 18 was to demo a poker tournament where 1st prize was $100, then that functionality is way beyond the scope of what this little sheet will do.
I think if you familiarize yourself with the sheet as I posted it, you might see it works well enough as is. Among other things, I turned on the "amount held out for 1st" cell, so now it will award 1st place any amount in that cell before it splits the remainder of the prize pool.
On my version, if you put all nine finishers in column G, and zero for the chips for anyone who is out, then the sheet acts as a "standings" report. If you don't want that then leave only players participating in the split in the yellow rows, but don't edit the green rows.
ASKER
I only changed the cells because the template didn't not return the right results (the way I pasted your forumla anyway). It did not automatically enter the smallest amount in the prize pool as the amount guaranteed to each player. It also didn't count the entires and list the amount of players correctly. Again, maybe I pasted the formula incorrectly as you definitley know more about this then me, but they work fine it coming up with the two results now.
Will my changes mess up anything?
How did you gray out every column after the N column? I'd like to do the same for all rows below 24. Also, how do you make the spreadsheet all white like you did (with no squares)?
Thanks.
Will my changes mess up anything?
How did you gray out every column after the N column? I'd like to do the same for all rows below 24. Also, how do you make the spreadsheet all white like you did (with no squares)?
Thanks.
I selected all the columns from O to IV and chose Format, Columns, Hide. I get rid of the gridlines by choosing Tools, Options, View, and clear the gridlines checkbox.
I believe you may have copied the formulas incorrectly or you have spaces in some cells that have yellow backgrounds, so they look empty but are really not. In my version, I couldn't get wrong results as long as I only changed yellow cells, and I always started entries on row 11 and went down from there.
Anyway, good luck, and don't go all in with rags.
I believe you may have copied the formulas incorrectly or you have spaces in some cells that have yellow backgrounds, so they look empty but are really not. In my version, I couldn't get wrong results as long as I only changed yellow cells, and I always started entries on row 11 and went down from there.
Anyway, good luck, and don't go all in with rags.
ASKER
http://spreadsheets.google.com/pub?key=pjh-nOW-MDelll-jqTbvIaA
The current spreadhseet does most of the calculations, but there is some error somewhere within the spreadhseet and I have no idea where the error is or how to find or correct it (that is why I posted here for help).
When I go to add additional players is where the erors come in so the way the spreadsheet is published at the link appears to be correct as is. The problem comes in when I change things and add extra players.
This spreadsheet published in the link will give you an idea of how the spreadsheet should perfom and how it should look.
Thanks again for any help.