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...