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!

 

[Fluff]

Which office version are you using?

It's 2019 judging by the profile.

 

[Legacy 143009]

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:

=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.

 

[Fluff]

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.

 

[jdubz1980]

Unfortunately I am using 2019, which I am finding is very lacking. I tried using google sheets and am getting a different output.

 

[jdubz1980]

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?

 

[Legacy 143009]

For that, convert every ($B$1/4) to only $B$1. Here find the sample file:

https://file.io/XxgaifgVcQqv

 

[jdubz1980]

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?

 

[Legacy 143009]

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.

 

[jdubz1980]

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.

 

[Legacy 143009]

I hope it did help. Take care