How to divide 100% by "x", but not equal increments?

[jaedmar]

I am creating a golf tournament purse distribution spreadsheet.

I need to divide the total purse among all of the winners, but in descending increments based on ranking in column A. For example, if a person is ranked "1" in column A, then he would get the greatest portion of the purse. The person ranked "2" would get a little less....

The amount of the purse is calculated and displayed in G2

The total number of winning positions is listed in G3

How do I do that?

 

[jaedmar]

MikeWx,
Yes, that is more along the lines of what I was thinking. I believe Biff was thinking that there was a set purse, but it may change as the total number of players changes.
What you mentioned about averaging the ties is exactly what I was thinking. That was the wrench I was going to throw into the whole thing.
The total purse (which I have tallied in G2) is to be divided among the top 50% of people entered (G3). G3 is equal to 1/2 the total people enrolled, rounded up.
Column A on the page 'Registered Players' list the number of each registrant, so the sum to which you refer is from A4 to A154 (as there are a total of 150 positions, but are not actually numbered until a person's name is entered).
I wouldn't expect anything to be left over after dividing among the top people. I want to divide specifically among the top 50% of the people, even if there are ties, so not necessarily the top "x" number of places?
What you recommend is perfect as it is.
With that in mind, I may now have an idea of how to write this, but would still surely appreciate your recommended formula.

 

[jaedmar]

Michael M,
I see exactly what you are saying. It is not as easy as first seems. Because of the potentially changing number of players, it is hard to say how much each person should be awarded. That is the only reason I was thinking to just award a portion of the total purse stepping it down as moving down the ranks. I understand that using this method, the greater the number of entrants, the smaller the separation of awards. The fewer the number of entrants, the greater the separation. I couldn't really figure how to do it based on percentages.

 

[Michael M]

Try this.
My resident Einstein, ( thank you Brucester) has provided me with the formula to do this a different way.
You Input 3 variables
Total Prize pool......the total purse
Prize ratio..............Can vary from 1 -100% 1% will give all the money to the winner, 99% will give an even distribution to all placegetters...the norm for an average field would be 70 %....Most Golf comps use 70%

No of prizes......the no of payouts

Sub Prizes()
Dim ANS1 As Long, ANS2 As Long, ANS3 As Long, c As Double, r As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("A10:B" & lr + 2).ClearContents
ANS1 = Application.InputBox("What is the Total Prize Pool")
ANS2 = Application.InputBox("What is the Prize Ratio in %")
ANS3 = Application.InputBox("How many Prize Recipients will there be ??")
 c = 2
Range("B6").Value = ANS1
    Range("B6").Style = "Currency"
Range("B7").Value = ANS2 / 100
    Range("B7").Style = "Percent"
Range("B8").Value = ANS3
Range("A10").Value = "1"
Range("B10").Formula = "=(B6*(1-B7))/(1-(B7^B8))"
Range("B10").NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-"
    For r = 11 To (10 + ANS3 - 1) Step 1
        Range("A" & r).Value = c
        With Range("B" & r)
            .Formula = "=$B$7*B" & r - 1 & ""
            .Style = "Currency"
            .NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-"
        End With
        c = c + 1
    Next r
Range("B" & 11 + ANS3).Formula = "=Sum(B10:B" & 9 + ANS3 & ")"
End Sub

 

[jaedmar]

I thought I was on the right track for a bit, but no luck. Some good ideas, but I encounter some sort of problem with each one.

As someone mentioned, much harder than it seems.

Again, here is (hopefully better) a description of what I am trying to do on this worksheet ('Official Results'). (I have also changed a couple reference cells from earlier to neaten things up).

I have a list of golf players. 50% of the total people registered will receive some portion of the total prize pot. So, my total number of positions to be paid out is equal to {(all the people registered/2)-.5} rounded up. The total payout positions is located on another worksheet ('payout & profit'!B7).

The total pot of prize money is also on that worksheet ('payout & profit'!G5).

After all of the registerd players' scores are entered, they are automatically ranked in Column A, from cell A2 to A152.

For reference, here are all the columns: A=Rankings, B=Player Name (from another worksheet), C=Registration # (from another worksheet), D=Total Score (from another worksheet), E=Payout, F=Percentage of total pot awarded.

I need to calculate columns E and F. If I can accurately figure one of them, the other should be easy.

So, what am I looking for...???
How can I calculate column F based on rank position? If a player ranks (A2:152) within the top 50% of players (cell 'payout & profit'!B7), he should be awarded a portion of the total pot (cell 'payout & profit'!G5). It should not be equal increments, however. There should be an offset. The person with the highest ranking (#1) should get the largest percentage. The percentage should drop a little with each rank, dwindling down to a very low percentage. Those percentages should be reported in Column F, cells F2 to F152.
If there are ties, I would hope that the percentages for each successive rank would be totaled then averaged for the amount of people holding the same rank. That is the least of my worries, though.
When the total percentages are added up, it should not equal more than 100% of the total pot ('payout & profit'!G5). A little less is acceptable if number need to be rounded down.

I hope I described that well enought to understand.

 

[Michael M]

Ok, did you try my code ??
It will do what you require.....
Winner gets largest prize !
Distribution is staggered !
Value adds exactly to 100 %

 

[jaedmar]

Okay, I will try that code again. What refereces do I need to change in the code?

 

[Michael M]

I'd suggest running the code in a new sheet first, so you see how it works.
Then, use the references in that new sheet to alter the code to suit the "new" location
If you get stuck, use the 2nd line of my tag to download HTML maker and post a sample of your data. We can then hopefully match it to fit.

 

[GolfLeague]

Can you send me this final spreadsheet calculation I am trying to mange a golf league payout when the number of player and pot change each week, thanks.