Prize pool distribution formula or script

[bsweet0us]

So, I am looking for a way to dynamically figure out how to distribute a prize fund based on certain user-defined variables. I stumbled across this thread which started down the path but I'm curious if what I'm trying to accomplish is feasible within the Excel framework.

The application is for a bowling tournament where the total number of entrants will determine the number of participants who receive a prize at the conclusion.

Here is the data the end user will provide:

Total number of participants
Entry fee
Portion of entry fee used to determine prize fund (The center takes a portion of the entry fee for providing the location)
Total number of prize recipients (This number is determined by dividing the number of participants by a user-defined number, typically between 3 and 8)
Percentage of prize fund allocated to first place

Constraint - No participant can receive a prize less than the entry fee and the prize fund is to be returned in full.

I would like for the spreadsheet to calculate the remainder of the prize fund once all data is entered. Ideally, the prize fund would reward higher places with a larger prize and scale down until the minimum allowable prize is reached. It is acceptable for multiple participants at the bottom of the prize list to receive an equal amount but would like to reward higher finishers with larger prizes, even if only incrementally.

The attached photo is a SIMPLE example of what I'm looking to accomplish. The values in column B (shaded light green) are entered by the user. The values in the yellow-shaded cells are formula derived. The cells shaded in blue are the ones I would like for Excel to dynamically calculate any time the user changes the value in any green-shaded cell.

Currently the blue-shaded cells have values that I would be comfortable with Excel dynamically generating, but are not beholden to those percentages.

I will be glad to provide additional details as necessary. Thanks in advance for taking on this project!

 

[jdellasala]

A couple of observations. The Percentage of Prize Fund totals 101%. You might want to knock a percent off somewhere. I suggest dropping 3rd place to 16% which brings the percentage to 100% and the 5th Prize to $105 with 20 Participants.
See if this helps:

Book1
	A	B	C	D	E	F	G	H	I
1
2								Prize Fund %	Prize Amount
3	# of Participants:	40		Places to Pay:	5		1st Place:	40%	$1,200.00
4							2nd Place:	27%	$810.00
5	Entry Fee:	$100.00		Total Prize Fund:	$3,000.00		3rd Place:	16%	$480.00
6					$1,000.00		4th Place:	10%	$300.00
7	Prize Fund:	$75.00			$4,000.00		5th Place:	7%	$210.00
8				Total Entry Fee:	$4,000.00		Total Prize$:	100%	$3,000.00
9	1st Place Allocation:	40%						Remaining Fund:	$1,000.00
10								Prize + Remaining:	$4,000.00
11								Total Fee:	$4,000.00
Sheet1

Cell Formulas
Range	Formula
E5	E5	=B3*B7
E6	E6	=(B3*B5)-E5
E7,I10	E7	=SUM(E5:E6)
E8	E8	=B3*B5
H8:I8	H8	=SUM(H3:H7)
I3:I7	I3	=$E$5*H3
I9	I9	=(B5*B3)-I8
I11	I11	=B3*B5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I10:I11	Expression	=$I$10<>$I$11	text	NO
I8	Expression	=$I$8>$E$5	text	NO

 

[bsweet0us]

Thanks for your reply and assistance, but I don't think I accurately conveyed what I am looking to accomplish.

In the example you provided, if there were 40 entrants and the parameters matched my problem (divide # of participants by 4), then we would have to figure a prize fund for 10 places, not 5.

What I am trying to get Excel to do is once we've determined the number of places to pay and what percentage of the total prize fund goes to first place only, figure out how to distribute the remaining funds while ensuring the last place to be paid returns at least the entry fee (cell B5 in your example).

I want cells I4:I??? to change dynamically when the percentage for first place is altered.

Using the numbers you provided (40 entrants, $3,000 total prize fund), the percentage for first place would have to diminish to around 25% to accommodate the additional payment places and still keep the 10th position at or above the $100 entry fee. So, something like the following would be dynamically calculated:

1st - $750 (25%)
2nd -$450 (15%)
3rd - $350 (11.66%)
4th - $300 (10%)
5th - $275 (9.16%)
6th - $250 (8.33%)
7th - $200 (6.66%)
8th - $175 (5.83%)
9th - $150 (5%)
10th - $100 (3.33%)

Now, if there were a way to generate a full prize list with recommended percentages/amounts as a starting point, that wouldn't be out of the question as long as the user would be able to adjust the first place payout/percentage and see how that changes the remaining positions.

This is not terribly difficult with smaller numbers, but as entries and payment places increase, the exercise becomes demonstrably more difficult.

 

[jdellasala]

I have never heard of a contest that had more places for prizes, and as you've discovered it becomes unmanageable! Typically if the pot increases, the prize or number of winners increases.
This is one way to determine prize values based on a MINIMUM of 20 participants (don't know what you do if you have fewer!):

Prize.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2								Prize Fund %	Prize Amount	# of Recipients	Total Prize$
3	# of Participants:	40		Places to Pay:	5		1st Place:	40%	$1,200.00	2	$1,200.00
4							2nd Place:	27%	$810.00	2	$810.00
5	Entry Fee:	$100.00		Total Prize Fund:	$3,000.00		3rd Place:	16%	$480.00	2	$480.00
6					$1,000.00		4th Place:	10%	$300.00	2	$300.00
7	Prize Fund:	$75.00			$4,000.00		5th Place:	7%	$210.00	2	$210.00
8				Total Entry Fee:	$4,000.00		Total Prize$:	100%	$3,000.00	10	$3,000.00
9	1st Place Allocation:	40%						Remaining Fund:	$1,000.00
10								Prize + Remaining:	$4,000.00
11								Total Fee:	$4,000.00
Sheet1

Cell Formulas
Range	Formula
J3:J7	J3	=ROUNDDOWN($B$3/20,0)
K3:K7	K3	=(I3*J3)/J3
H8:K8	J8	=SUM(J3:J7)
E5	E5	=B3*B7
E6	E6	=(B3*B5)-E5
E7,I10	E7	=SUM(E5:E6)
E8	E8	=B3*B5
I3:I7	I3	=$E$5*H3
I9	I9	=(B5*B3)-I8
I11	I11	=B3*B5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I10:I11	Expression	=$I$10<>$I$11	text	NO
I8	Expression	=$I$8>$E$5	text	NO

 

[bsweet0us]

Yeah, I feel like this might be an exercise too great even for Excel. I appreciate your assistance and attempt to find a solution, but I might just have to rethink my approach.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need help with a prize pool distribution formula or script
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Legacy 143009]

Hi,
Here is a logarithmic function for only the distribution of the prize to the participants. You should figure out the rest on your own. I think this is the most important part. The formula resizes itself according to the number of participants. I am also sharing a sample file. You may play around with the number of participants.
C1:

=LET(s,($B$1/4),f,$B$2*LOG(C1/(s+1))*(1/SUM(LOG(OFFSET($C$1,0,0,s)/(s+1)))),IF(ISNUMBER(C1),IF(ISNUMBER(C2),IF(C1<=s/2,FLOOR(f,25),CEILING(f,25)),$B$2-SUM(OFFSET($D1,-1,0,(s-1)*-1))),""))

WeTransfer | Send Large Files Fast - Up To 2GB Free

The simple, quick and secure way to send your files around the world without an account. Share your files, photos, and videos today for free.

we.tl

 

[Legacy 143009]

This gives a better result:

=LET(s,($B$1/4),f,$B$2*LOG(C1/(s+1))*(1/SUM(LOG(OFFSET($C$1,0,0,s)/(s+1)))),IF(ISNUMBER(C1),IF(ISNUMBER(C2),IF(MOD(C1,2)<>0,FLOOR(f,25),CEILING(f,25)),$B$2-SUM(OFFSET($D1,-1,0,(s-1)*-1))),""))

 

[jdubz1980]

Flashbond,
Would you repost your sample spreadsheet with the updated formula please? I have been looking for something exactly like this for my bowling league.

Thanks,
Jdubz

 

[Legacy 143009]

Which office version are you using? If you are using office 365, you can directly use the formula in post#8 with the setup above. You should fill the values in column B and C manually. You will paste the formula in D1 and pull down.