Prize pool distribution formula or script

bsweet0us

New Member
Joined
Apr 12, 2008
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • Excel snip.png
    Excel snip.png
    24 KB · Views: 193

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
ABCDEFGHI
1
2Prize Fund %Prize Amount
3# of Participants: 40Places to Pay: 51st Place: 40%$1,200.00
42nd Place: 27%$810.00
5Entry Fee: $100.00Total Prize Fund: $3,000.003rd Place: 16%$480.00
6$1,000.004th Place: 10%$300.00
7Prize Fund: $75.00$4,000.005th Place: 7%$210.00
8Total Entry Fee: $4,000.00Total Prize$: 100%$3,000.00
91st Place Allocation: 40%Remaining Fund: $1,000.00
10Prize + Remaining: $4,000.00
11Total Fee: $4,000.00
Sheet1
Cell Formulas
RangeFormula
E5E5=B3*B7
E6E6=(B3*B5)-E5
E7,I10E7=SUM(E5:E6)
E8E8=B3*B5
H8:I8H8=SUM(H3:H7)
I3:I7I3=$E$5*H3
I9I9=(B5*B3)-I8
I11I11=B3*B5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I10:I11Expression=$I$10<>$I$11textNO
I8Expression=$I$8>$E$5textNO
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJK
1
2Prize Fund %Prize Amount# of RecipientsTotal Prize$
3# of Participants: 40Places to Pay: 51st Place: 40%$1,200.002$1,200.00
42nd Place: 27%$810.002$810.00
5Entry Fee: $100.00Total Prize Fund: $3,000.003rd Place: 16%$480.002$480.00
6$1,000.004th Place: 10%$300.002$300.00
7Prize Fund: $75.00$4,000.005th Place: 7%$210.002$210.00
8Total Entry Fee: $4,000.00Total Prize$: 100%$3,000.0010$3,000.00
91st Place Allocation: 40%Remaining Fund: $1,000.00
10Prize + Remaining: $4,000.00
11Total Fee: $4,000.00
Sheet1
Cell Formulas
RangeFormula
J3:J7J3=ROUNDDOWN($B$3/20,0)
K3:K7K3=(I3*J3)/J3
H8:K8J8=SUM(J3:J7)
E5E5=B3*B7
E6E6=(B3*B5)-E5
E7,I10E7=SUM(E5:E6)
E8E8=B3*B5
I3:I7I3=$E$5*H3
I9I9=(B5*B3)-I8
I11I11=B3*B5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I10:I11Expression=$I$10<>$I$11textNO
I8Expression=$I$8>$E$5textNO
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Excel Formula:
=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))),""))
1677227995873.png
 
Upvote 0
This gives a better result:
Excel Formula:
=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))),""))
 
Last edited by a moderator:
Upvote 0
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
 
Upvote 0
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.
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,727
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top