Calculating prize pool payouts

[towners]

Hi,

I run a small poker tournament and wanted to automate the calculation of the final cash prizes. I've created a lookup that calculates the prizes based on the number of people and the prize pool. e.g for less than 8 people only 2 people get paid split 60%/40%. Between 12 & 20 4 places are paid and split as in the image below, and so on.

This is all fine however, no-one carries small change anymore and it's all folding notes... So I want to round up or down the prizes to the nearest £10 so it's not as straightforward as just taking a percentage, the percentage only becomes a guide.

Can anyone help with a formula that can take the lowest denomination of £ being £10?

Many thanks,

PT

 

[t0ny84]

Do you have the full breakdown of how the prize money is split and does everyone get a prize or only the top 10 players?

E.g
1st
- If under 5 players then prize is 60%.
- Over 5 players then prize is 50%.

2nd
- If under 5 players then prize is 40%.
- Over 5 players then prize is 30%.

3rd
- If under 5 players then prize is 20%.
- Over 5 players then prize is 10%.

4th and 5th
- If under 5 players then prize is 10%.
- Over 5 players then prize is 5%.

 

[KevCarter]

I think the ROUND function handles what you want. Does this help?

Book1
	C	D
3	Total Pay	$1,110.00
4
5	40%	$440.00
6	27%	$300.00
7	19%	$210.00
8	14%	$160.00
Sheet1

Cell Formulas
Range	Formula
D5:D8	D5	=ROUND($D$3*C5,-1)

 

[towners]

Do you have the full breakdown of how the prize money is split and does everyone get a prize or only the top 10 players?

E.g
1st
- If under 5 players then prize is 60%.
- Over 5 players then prize is 50%.

2nd
- If under 5 players then prize is 40%.
- Over 5 players then prize is 30%.

3rd
- If under 5 players then prize is 20%.
- Over 5 players then prize is 10%.

4th and 5th
- If under 5 players then prize is 10%.
- Over 5 players then prize is 5%.

Hi, thanks for replying.

Here's the lookup table, I use a HLOOKUP to find the prize %

 

[towners]

I think the ROUND function handles what you want. Does this help?

Book1
	C	D
3	Total Pay	$1,110.00
4
5	40%	$440.00
6	27%	$300.00
7	19%	$210.00
8	14%	$160.00
Sheet1

Cell Formulas
Range	Formula
D5:D8	D5	=ROUND($D$3*C5,-1)

Hi, thanks for replying.

I also used the round function but the problem I have is altering the percentages for 2nd, 3rd & 4th to suit once the first prize value has been rounded, then 3rd & 4th once the second prize has been rounded and so on.

I'm thinking the simplest way might be to round all based on percentages and then calculate the difference between the total of the rounded prize values from the total pool and apply this difference to the first placed prize money

 

[t0ny84]

I think

=Round((Percentage*Total Funds),-1)

would be the easiest, the below picture is a comparison using the percentages from column J in your screenshot. The ROUND function evens out the amount.