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

FISH wrote:
> 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.

--
Jordon

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" <(E-Mail Removed)> wrote in message
news:wUF5i.6883$(E-Mail Removed)...
>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 !!!
>
>

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" <deitbe @ consumer dot org> wrote in message
news:%23T%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:wUF5i.6883$(E-Mail Removed)...
>>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 !!!
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:%fJ5i.3222$(E-Mail Removed)...
> 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" <deitbe @ consumer dot org> wrote in message
> news:%23T%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:wUF5i.6883$(E-Mail Removed)...
>>>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 !!!
>>>
>>>
>>
>>
>
>

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" <deitbe @ consumer dot org> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%fJ5i.3222$(E-Mail Removed)...
>> 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" <deitbe @ consumer dot org> wrote in message
>> news:%23T%(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:wUF5i.6883$(E-Mail Removed)...
>>>>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 !!!
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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


"FISH" <(E-Mail Removed)> wrote in message
news:UXJ5i.3239$(E-Mail Removed)...
> 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" <deitbe @ consumer dot org> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%fJ5i.3222$(E-Mail Removed)...
>>> 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" <deitbe @ consumer dot org> wrote in message
>>> news:%23T%(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:wUF5i.6883$(E-Mail Removed)...
>>>>>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 !!!
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>