Thread: Looking for formula for curved distribution

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!

Originally Posted by Zxurian

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!

You want the first prize to be:



the k-th prize is:



Then the number of regular prizes is the smallest such that:



There will be additional supplementary prizes each of $1 to that:



Then the total number of winners is

CB

Thank you for the reply!

I tried testing your formula in my sheet but couldn't get a result set that I could work with. It looks like your on the right track though, closer than I got anyway. There's only two problems with it. First, is that the sum of prizes given to the winners doesn't equal the total number of prizes available. Second, when the number of winners start creeping up, at 9 winners, with TP = 36, 9th place is getting 0, when it should at least be getting 1.

I'm going to try restating my stipulations using the math symbols that you used and hopefully that will help lead to a solution.

The sum of prizes distributed to players must equal a fixed value of the total prizes available, such that


also, the number of prizes given to place must be greater than or equal to 1, such that


I believe I got those formulas right. The payout curve looked like it was exactly what I was looking for, except for the two problems stated above.

Thank you for the reply and assistance!