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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
It's 2019 judging by the profile. ;)
Oh, you are right Fluff. It is irrelevant with the question sorry but this is a topic that I am willing to mention for a long time. In the mobile site, office versions are not visible on the post view. You should navigate to user profile and then about...

Please advise the developers that to place office versions right under the user rank. It would be great 🙏

Beside this, about the question :) 2019 formula should be something like:
Excel Formula:
=IF(ISNUMBER(C1),IF(ISNUMBER(C2),IF(MOD(C1,2)<>0,FLOOR($B$2*LOG(C1/(($B$1/4)+1))*(1/SUM(LOG(OFFSET($C$1,0,0,($B$1/4))/(($B$1/4)+1)))),25),CEILING($B$2*LOG(C1/(($B$1/4)+1))*(1/SUM(LOG(OFFSET($C$1,0,0,($B$1/4))/(($B$1/4)+1)))),25)),$B$2-SUM(OFFSET($D1,-1,0,(($B$1/4)-1)*-1))),"")
This is an array formula so bear in mind that tou should press Ctrl+Shift+Enter together after paste.
 
Last edited by a moderator:
Upvote 0
In the mobile site, office versions are not visible on the post view.
I had forgotten about that, and unfortunately there is not much that can be done about it.
 
Upvote 0
Unfortunately I am using 2019, which I am finding is very lacking. I tried using google sheets and am getting a different output.
 

Attachments

  • Screenshot 2023-04-18 150010.jpg
    Screenshot 2023-04-18 150010.jpg
    122.1 KB · Views: 10
Upvote 0
Unfortunately I am using 2019, which I am finding is very lacking. I tried using google sheets and am getting a different output.
Fluff/Flash,
I was able to duplicate flash's spreadsheet. My last question I have is what do I need to change in the formula so that the distribution can span across all participants and not just the top 5?
 
Upvote 0
Flash,
Thanks again. I figured that out 2 seconds after I replied. After that I realized that I need one other thing. I would like to add a user input to adjust the severity of the spread. Essentially compressing the curve and lessening the difference between the first and last places. Is that something that can be easily done?
 
Upvote 0
I can't remember the math behind, exactly. But when Iook at it again, at the first glimpse, I can say you can add small adjustments after every ($B$1+1) like ($B$1+1)*0.9. This should change how steep is the slope.

Also you can adjust the "round"ness by playing with 25s.
 

Attachments

  • 1681848925064.png
    1681848925064.png
    87.4 KB · Views: 20
Last edited by a moderator:
Upvote 0
Flash,
Thanks again. I think i have enough of an understanding that i should be able to play with it and get what I need.
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

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