Hi guys,
i would appreciate your help with the below.
I am to create a prize pool (as part of my mastery project) and I am a bit stuck.
So, I would like if possible to get a formula where excel will automatically distribute the winning from a prize pool for n amount of participants with 10% (of participants) paid. Just like a poker tournament prize pool. I have attached a manual one I made for reference, but imagine if i had e.g. 1mil participants.
Thank you in advance.
It is far from clear what you expect to see!! So this is a total guess.
Adjust the input values (blue) in column P... the correct results are generated in column M... formula in M2, dragged down:
=IFERROR($P$4*INDEX(B2:L2,MATCH($P$2,LEFT($B$1:$L$1,SEARCH(" ",$B$1:$L$1))+0,1)),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi Glenn,
Thank you for assisting and apologies for not explaining properly.
I am trying to create an automated model for a prize pool distribution. So I will have participants entering the draw with $2 and depending on the number of participants, the winnings will be split following the standard "poker tournament" breakdown, meaning 1st gets 20%, 2nd 15%, etc. up until the 10% of participants get paid. But the number of participants can vary massively from e.g. 10 - 10.000.000. There is no minimum or maximum number of participants or winners. Therefore how can I use excel to create a formula that would breakdown winners and percentage of winnings?
- No minimum - No maximum number of participants
- $2 entry fee
- 10% of participants win
I have no idea what a standard "poker tournament" breakdown is. Please elaborate.
the standard poker tournament breakdown is: the more participants that join the more places are getting paid, but the distribution of money is getting split to more positions. Meaning, lets assume there are 100 participants with 20 positions paid, 1st position would get 25% - 2nd position would get 12.5% - 3rd 7% etc. - 20th position would get 0.75%. Now in the same scenario if more people joined the tournament and lest assume a total of 200 participants, the positions paid would be 40 and the prize pool would be divided; 1st 20% - 2nd 9.5% - 3rd 4% - 40th 0.3%. as an example
Now if the number of participants is small, i can calculate the % manually but what if we have 1.000.000 participants with 10% of them getting paid. Is there a way to automatically calculate those % of money allocation per position?
No. That does not help. What is the mathematical relationship between:
No of participants
No of cash winners
Amount paid to winners
You said:
With 100 participants, 20 get paid (why 20?)
1st position would get 25% - 2nd position would get 12.5% - 3rd 7% etc. - 20th position would get 0.75%.
Why 25? Is 2nd 50% of 1st? Third is ??? of 2nd, Fourth is ???% of 3rd, etc. Without RULES this is impossible.
Glenn, thank you but I don't think you get it. The numbers I used above are just an example without any particular relationship. I am trying to create that relationship but the scale is very big and its quite complicated to do so, therefore i was wondering if there is a function in excel that would be able to "produce that relationship". I would like to create the attached but on the thousands and millions scale.
I can break it down in for example; the top 10 will always split 50% of the total pool with 1st place getting 33%, 2nd 22%, 3rd 15.50%, and goes on to share the 100% of the 50% with any logical relationship between the percentage allocation. Then the rest of all the participants can share the remaining 50% of the pool equally, but I would prefer a better distribution.
Unfortunately when it comes to accounting for example 100k winners the formula is difficult to figure out. The relationship between the percentages/numbers is irrelevant atm as the most important is to figure out if there is something that can help build the model. I can tell you that 2nd position will get 50% of the 1st but that will drop as the prize pool and participants grows with more places paid and larger distribution
Attachment 767966
Last edited by Tdel8; 02-13-2022 at 11:44 AM.
I don't think you get how this works! Members will use the sample data and the expected results you provide to help work out their solution.Glenn, thank you but I don't think you get it. The numbers I used above are just an example without any particular relationship.
I don't think you realise just how unhelpful it was of you to provide random results that do not match the requirement. Hardly surprising, then, that you have left Glenn scratching his head (and me shaking mine).
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
see image attached, the relationship between the numbers is irrelevant atm, it can be any as it will constantly be changing depending on the number of participants. any relationship would do to build the model
Last edited by Tdel8; 02-13-2022 at 11:52 AM.
I have no idea what you want. Clearly the first sheet bears no resemblance to what you want. So... unless you can mock up what you expect to see in a way that I can follow... I'm out.
no worries, thank you very much anyways for your time
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks