Calculating quantity required to reach an amount

[dasadler]

I am trying to calculate the number of various denominations needed to reach a stated amount. For example, if the amount is $1000, how many $500 bills, $100, $50, $20, $10, $5 would be needed?

More specifically - and my immediate need:

If I have a poker table with a variable number of players (usually 9) and a starting amount of poker chips (usually $1,000) how many chips of each color (denomination) would I need per player and for the table?

I would like to be able to change the starting amount and the number of players and have the spreadsheet tell me how many chips I need. See attached spreadsheet.

[HansV]

The easiest solution would be to give each player one purple ($1000) chip, but that's probably not what you want. I assume that you have some unstated requirements - could you try to describe how you'd like the chips (denominations) to be distributed?

[dasadler]

The only unstated requirement I can think of would be to have more smaller denomination chips. The distribution in the example is manually calculated and represents the optimum distribution for 1000. The intent is to avoid a huge stack of chips for each player yet enough for play. If smaller denominations are needed by a player, the Dealer will make change.

If the starting amount were 500, then the optimum distribution would be the same as 1000 but without the yellow chip.

[HansV]

But if you want more smaller denomination chips, why not give the player 5 white ($1) chips + 4 red ($5) chips instead of 5 red chips?

The problem is that you have some intuitive idea of what constitutes the "optimal" distribution, but we can't use intuition to create a formula or macro.

[Jezza]

Are we talking about something similar to Ramanujan's 'Simple' Pattern?

or New Scientist here

[dasadler]

Maybe this does not lend itself to an Excel solution. It is true that there is some intuition involved. The 1000 and 9 assumptions are what we normally do in our business where we don't use $1 chips (not in poker). I was hoping to come up with a solution that home players might use where they can change the denominations to much smaller amounts. In other words, a weekly poker game where each player buys in for $5 or $10 and the chip values are in cents, not dollars.

Ideally, the home player could assign chip values, put in the number of players, indicate the starting amount and the spreadsheet would display the optimum distribution.

It is simple to do manually but I just don't know how to express it in a form that can be used to develop a solution.

[dasadler]

Jerry,

I think is is related to partition numbers in some way. Now to figure out how to exploit that relationship to a solution.

[HansV]

The 9 players and $1000 amount aren't important - it's easy to modify formulas for changing numbers of players and amounts once you know how you want to distribute the chips. You'll have to come up with some kind of specification for the distribution, e.g. "the player should be able to give change from any amount up to the limit".

[dasadler]

Okay, I think I understand. What if we were to include table limits? For example if the table allows wagers from $5 (min) to $200 (max), the player should be able to make any wager within that range. We would want to be able to change the limits since a home game might have a 5 cent minimum and one dollar maximum.

Obviously, the chip denominations would be changed to match the game. I think it is good to list all the chip colors yest be able to specify which denominations will be used in that game. In my first example, I did not distribute any $1 (white) chips since we don't normally use them in out games however another game with different lints would likely assign different values to the various color chips. Maybe a check box beside each chip indicating if it would be used?

[HansV]

It's up to you to decide whether you want to specify a minimum and maximum, so that only chips with a value within that range will be used, or explicitly specify which chips are to be used and which aren't. Whatever suits your purpose best.

[steveh]

I am trying to calculate the number of various denominations needed to reach a stated amount. For example, if the amount is $1000, how many $500 bills, $100, $50, $20, $10, $5 would be needed?

More specifically - and my immediate need:

If I have a poker table with a variable number of players (usually 9) and a starting amount of poker chips (usually $1,000) how many chips of each color (denomination) would I need per player and for the table?

I would like to be able to change the starting amount and the number of players and have the spreadsheet tell me how many chips I need. See attached spreadsheet.

Hi

I know little about poker, and the moderators at their annual Christmas kneesup probably say less about Excel

My immediate thought was could a rand function be used but I quickly screwed up that plan and sent it to the bin, is there anything here on this home poker site that might be used, adapted etc. http://www.homepokertourney.com/poker-utilities.htm" onclick="window.open(this.href);return false;

[dasadler]

I think your suggestion of using chips with a value that falls within the table limits is the best way to go.

I am not sure how to logically describe it, though when the starting amount is greater than the max limit.

[HansV]

In C8: =IF(OR($B8>$D$5,$B8<$D$4),0,INT(($D$2-SUMPRODUCT($C9:$C$14*$B9:$B$14)-0.005*(N($B7)>=$D$4))/$B8))
Fill down to C13.

See attached version.

Poker Chips.xlsx

[dasadler]

Thank you Hans. This works, of course yet it provides more black chips than desired and no yellow chip. Let me try to explain my thinking on this as I think the original logic was faulty.

if the chip value is equal to or greater than the table min wager, then use this chip if possible (without exceeding the starting amount).

For those chips that will be used... starting at the lowest denomination... distribute enough chips to match the value of the next denomination (5 reds = 1 green). Then if the sumproduct of chips distributed so far is below the starting amount then go to the next denomination...

I do not find the words to describe this logic but basicalliy enough of a lower chip to match the next higher chip. Each time this is doen, see if the total value of chips distributed matches the starting amount - if so, stop. If not, check to see if the difference between the value of chips already distributed and the starting amount is greater than or equal to the next denomination chip.

I having trouble expressing myself with this. Let me try again..

presuming the chip value is greater than or equal to the minimum wager:

give enough whites to equal one red.

give enough reds such that along with the whites aleady distributed, it equals one green.

give enough greens such that along with the whites and reds aleady distributed, it equals one black.

give enough blacks such that along with the whites, reds, and greens aleady distributed, it equals one yellow.

give enough yellows such that along with the whites, reds, greens, and blacks aleady distributed, it equals one purple.

give enough purples such that along with the whites, reds, greens, blacks, and yellows aleady distributed, it equals the starting amount.

This is, I think, the proper logic although if the starting amount is 1200, there should be two additional blacks. or there should enough additional chips of the highest value given to make up the difference.

[HansV]

My reasoning for not providing a yellow chip in the sample workbook is that the player wouldn't want to have a $500 chip if the maximum wager is $200. So the player gets more $100 chips which (s)he can use.
Do you want the player to get $500 chips even if the maximum wager is $200?

[dasadler]

Your reasoning was sound but based on the flawed logic I presented (that only chips within the wager limits would be used).

Yes, the players can have chips with values greater than the minimum wager. A poker hand has several wager opportunities and only each opportunity is subject to table limits so... if there are four wager opportunities and 200 is wagered each time, it would exceed the limit for one wager but the hand (or game) itself is not subject to the limit.

This is what I meant when I said the original logic was flawed or incomplete.

Generally speaking, a player is given (or buys in) more chips than will be needed for a single hand or game. The expectation is that the initial chip distribution (which is the question here) should be sufficient to give the player their full chip value without having a huge stack of chips to manage and allow the player to play several hands before needing change.

[HansV]

Does this mean that we can completely ignore the maximum wager?

[VegasNath]

Does this mean that we can completely ignore the maximum wager?

Hope nobody ninds my 2cents...
My guess would be Yes. When the OP talks about wagers, I think they are talking about the 'blinds'. These are compulary blind bets, regardless of the quality of the hand that you are dealt.

A poker game has a small and big blind. EG, small blind may be 25, big blind 50. After x time, the blinds increase to 50 & 100, and so on, (usually doubling up). There may be a maximum blind, but in 'no-limit' games, there is no maximum bet, a player can throw all chips into the pot.

In a game with starting blinds of 25, 1000 chips per player, I would probably use:
25 x4, 50 x4, 100 x2, 500 x1
or
25 x4, 50 x4, 100 x3, 200 x2

You need enough chips to keep you going, but when betting increases, don't want loads of small chips on the table.
Which reminds me, must get the boys 'round!

[dasadler]

@Nathan: Actually, I was referring to the wagers and not the blinds. Although Texas Hold 'Em Poker (which has blinds) is a popular poker game, it is one of several we play and I was trying to come up with a solution that would apply to any flavor of poker.

@Hans: Yes Hans, I think we can ignore the upper wager limit. Indeed, using the min wager is convenient though somewhat artificial because if the player had to meet a $5 minimum wager, they do do it using five $1 chips. I think it is still a good guideline since it would rarely happen and we typically do not use $1 chips in Poker (though technically we could).

[HansV]

I don't see how your algorithm would work if the starting amount is not equal to the value of one of the chips. If we don't take the maximum wager into account, the best I can do is

=IF($B8<$D$4,0,INT(($D$2-SUMPRODUCT($C9:$C$14*$B9:$B$14)-0.005*(N($B7)>=$D$4))/$B8))

in C8 and fill down. See the attached version.

Poker Chips2.xlsx