I'm not sure what category this would fall under, but I'm assuming Applied Mathematics would fit. Apologies if it's not the correct forum.
I'm trying to find a formula to plug into excel that I can use for a curved distribution (for prizes distributed to players), but for the life of me, I can't figure one out. I've tried playing with exponential curves & percentages, but can't get a universal formula that will work for any number of players.
The known variables are as follows:
Total Number of Winners [NW]
Total Prizes [TP]
Rank from top (1st, 2nd, 3rd, etc...)
The prize distribution needs to resemble a 1/x curve where people ranked higher get a greater number of prizes then people ranked lower.
Example Output with TNW = 5, TP = 20
1st = 8, 2nd = 5, 3rd = 3, 4th = 2, 5th = 2
Example Output with TNW = 7, TP = 30
1st = 12, 2nd = 7, 3rd = 4, 4th = 3, 5th = 2, 6th = 1, 7th = 1
TNW & TP can both vary, which is why they have to be variables in the equation and not fixed values.
The only stipulation is that someone ranked in a tournament with a higher number of TNW, can't get less prizes than someone ranked in a tournament with a lower TNW. (Ex. someone placing 3rd in a tournament with 10 people should get at least as much as someone placing 3rd in a tournament with 6 people.)
The other problem with the formula is that I need to be able to pull an individual number from the distribution for the prizes going to that rank. ie, if there are 10 players in a tournament, then i need to list the number of prizes going to each rank (1st, 2nd, 3rd, etc...) in their own cell, so the formula would need to find out how many prizes 1st would get, out of a total distribution curve for all the players.
Any assistance on this would be appreciated as I've scoured the interwebs looking for guidance and I'm coming up empty.
I've subscribed to this thread so if any more information is needed, I'll post here as soon as possible.
Thank you in advance for any support!