Percentages problem

**Ridgy** · 01-26-2010, 09:34 PM

I go to a few regular poker games every week. The number attending varies from week to week as does the buy-in amount to play so I've compiled a few tables to help calculate the amount to pay out for each position in £'s to help the organizer.

The number of places paid out depends on the number playing. The percentage of the total pot paid to each winner also varies depending on the number playing.

E.g up to 8 playing and 2 places are paid in a 70% 30% split of the total pot.
up to 40 playing and 5 places are paid in a 45% 25% 15% 10% 5% split of the total pot

My problem is that the formulas I've used given my limited skill with excel mean that sometimes when all the payouts are counted up they exceed the pot total, usually by £1.

For example in the second table (£15 buy-in) 5 people are playing for a total pot of £75. The 70/30 split is given as £53 and £23 which exceeds the amount available by £1. This is repeated throughout the whole file in various places.

How can I make it so that the amount shown to be paid out never exceeds the total money available but sticking (roughly) to the percentages indicated? All amounts to be paid must also be to the nearest £1.

I don't have my laptop at the tournaments or I could just use the top table and substitute the figure in B3 to whatever the buy-in amount is and it should work fine (apart from the above mentioned problem). I need this to be printed out and therefore it needs to cover all combinations we might use

**NBVC** · 01-26-2010, 10:43 PM

The most exact way might be to make the bottom row the difference of the Pot and the Positions above

e.g.

For for 2 places, and £15 pot, the bottom formula would be =B14-B15 copied across

for 3 places, =J14-Sum(J15:J16) copied across

for 4 places, =V14-Sum(V15:V17) copied across

and for 5 places, =AF14-Sum(AF15:AF18) copied across

**Ridgy** · 01-27-2010, 05:44 PM

Hi NBVC

Thanks for the quick response. Your suggestion sure makes sense and ought to work however I have tried it and it still seems to give similar results to the original formula.

For instance on the top table 31 people playing in a £10 buy-in event playing for £310 the payouts are given as £140(45%), £78(25%), £47(15%), £31(10%), £16(5%) which adds up to £312.

Curiously when I left click and highlight the column in question the sum given on the status bar is £310!

I have left Sheet 1 in original form and copied it over to Sheet 2 for editing. I've changed the top table only to the new formula. Have I copied it over correctly?

Feel free to alter Sheet 2 in any way you see fit. I've attached the file again (renamed).

**NBVC** · 01-27-2010, 05:55 PM

Although your percentages are showing as whole numbers, in fact, when you multiply 310 * 45% , the answer is actually: 139.5 not 140

So perhaps you need to use ROUND() function on all the cells above the last rows of each table.. in the last row you can keep the sum formula i gave previously.

so for example.. cell AF4 would be =ROUND(45%*AF3,0)

AF5: =ROUND(25%*AF3,0)

AF6: =ROUND(15%*AF3,0)

AF7: =ROUND(10%*AF3,0)

AF8: =AF3-SUM(AF4:AF7)

**Ridgy** · 01-27-2010, 08:15 PM

NBVC

Thank you so much, thats perfect!

I've done the first two tables is there a quicker way I can do this instead of changing the formulas in the first column each time and copying them across five times per table?

**NBVC** · 01-27-2010, 10:25 PM

Actually, since you did a great organizational job on your tables and they are all the same sizes and the formulas are all in the same locations and the formulas are all relative (i.e. dependent on position not on fixed points), then it is quite easy to duplicate the formulas down..

Just select and copy from B4 to AY9.. Go to B15 and Paste (CTRL+V)

Then go to B26 and CTRL+V, then to B37 and ... you guessed it.. CTRL+V... get the pattern?