Any easy way to do this with Excel?

[FISH]

I play tournament poker and run into situations from time to time where I
need to figure some calculations quickly. A situation comes up in tournament
poker when players left at the end of a tournament start to talk about
splitting up the remaining prize money.



The players with more chips should get more of the prize money as their chip
stack has more equity then a player with a smaller chip stack. I'd like to
create a spreadsheet that will calculate what each person should make in a
perfectly even split based on the equity they hold in their stacks.





The standard deal calculation is to look out how much money is still at
stake -- in other words, assign everyone left in the field the lowest
remaining prize and see what's left over -- and then distribute the
remaining funds proportionately according to the chip stacks.





For example:



Let's say three players are left that will be involved in the potential deal
with stacks of 200k for Player 1, 170k for Player 2, and 130k for Player 3
(500k total). The prize money is $1000 for 1st, $700 for 2nd, $500 for 3rd.
Each player gets at least $500 at this point (as they have already
guaranteed 3rd place money), so there's only $700 remaining in play
(1000+700+500-3*500). So the deal amounts based completely on the equity
each player has in his chip stack 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





The amount of players involved in the "deal" could be anywhere from 2-9. I
want to set up the spreadsheet to where all I need to enter is the remaining
players chip count and the prize money breakdown. The spreadsheet would then
need to calculate from the remaining prize money, how much is left for each
player to play for and what % of that amount each player deserves based on
their chip stacks (their chip stack / total chips in play).







So, is there an easy way to make a spreadsheet that will allow me to figure
these calculations?





THANKS in advance for any help !!!

 

[Jordon]

The amount of players involved in the "deal" could be anywhere from 2-9. I
want to set up the spreadsheet to where all I need to enter is the remaining
players chip count and the prize money breakdown. The spreadsheet would then
need to calculate from the remaining prize money, how much is left for each
player to play for and what % of that amount each player deserves based on
their chip stacks (their chip stack / total chips in play).

In a1 through a9 enter each players winnings.
In a10 sum the winnings.
In b1 enter =a1/$a$9 and format as a percentage.
Copy that down to b9.
In d1 enter the total prize money.
In c1 enter =$d$1*b1.
Copy that down to c9.

 

[Bernie Deitrick]

FISH,

What happens if there are 5 players still remaining, a total pool of $2,200,
and a third place of $500? Since they can't all get $500, how is the pool
split then? Proportionately, or based on some lesser constant (80% of the
3rd place award, then split, or......)?

Bernie

 

[FISH]

Hey Bernie,

If I understand your question correctly, you are asking what happens when
there are 5 players left and the tournament only pays the top 3 places?

If that is the case, you would take the total prize pool as the amount up to
be split as no one is guaranteed anything at that point. So in this
scenario, the full $2,200 prize pool would be split among the 5 remaining
players based on their percentage of chips divided by the total amount of
chips in play. So if Player 1 here still had 40% of the total chips, he
would earn $880.

 

[Bernie Deitrick]

FISH,

OK. That makes sense.

In cell A1, enter the total purse: $2,200
In cell A2, enter the lowest remaining price: in your example, $500
In cell A3 and on down the column, enter the names of all contestants.
In cell B3 and down, enter the chips amount, with 0 for those that are no
longer in the running.
Then in cell C3, enter the formula:

=IF(B3 > 0, IF(COUNTIF(B:B,">0")*$A$2<$A$1,$A$2 +B3/SUM(B:B)*(
$A$1-COUNTIF(B:B,">0")*$A$2),B3/SUM(B:B)*$A$1),0)

and copy that down to match your list.

HTH,
Bernie
MS Excel MVP

 

[FISH]

Thank you very much. This is great already.

Is there any way to make have this spreadsheet take it one step further. I'm
just trying to get it to where I only have to manually enter the least
possible amount in order to get the information I need. If/when this
situation comes up, I will need the information ASAP and the longer it takes
me to enter the information, the longer I will be "away" from the actual
tournament and not seeing what is going on.


Is there any way to have the spreadsheet figure in the amount guaranteed to
each player based on the players (and their stacks) I input. So for example,
let's say I have 9 spots on the spreadsheet from B3-B11(as that would be the
max amount of players a deal might be made between), but I only enter in 3
players and their chip counts (because in this example there are only 3
players involved). I then have the prize money breakdown listed for 1st
place in E3 through 9th place in E11.

Can I then add another function somewhere in the spreadsheet so it will then
automatically take the fact I only entered in 3 players and figure each of
these three players are guaranteed at least 3rd place money which is listed
in E5?


If I can get the spreadsheet to do this function, all I will have to input
is the chip counts for each player remaining that is involved in the deal.



THANKS again...

 

[Bernie Deitrick]

FISH,

If you only enter two amounts in column B, those two only will be factored
into the draw, as long as you enter the second place money award into cell
A2, and the total of 1st and 2nd places into A1. That is what the first IF
conditional does. Just enter 0's into all the other cells.

Bernie