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

jaedmar said:

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?

Click to expand...

Try this...

Let's assume this is your leaderboard:

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style=" border-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Score</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Rank</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Joe</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">66</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Bill</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">68</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Tom</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">68</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">John</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">68</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Tim</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">69</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Jim</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">69</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Loe</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">69</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Bob</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">71</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">8</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Lee</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">71</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">8</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Eric</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">73</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">10</td></tr></table> <br /><br />

This is the prize/point distribution:

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >H</td><td >I</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">Rank</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Points</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">10</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">9</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">8</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">7</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">6</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">4</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">9</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">10</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr></table> <br /><br />
Enter this formula in D2 and copy down to D11:

=AVERAGE(OFFSET(I$2,MATCH(C2,H$2:H$11)-1,,COUNTIF(C$2:C$11,C2)))

Although I don't think that is actually what I was trying to do, I did give it a shot, but it did not seem to work.

What I am trying to do is to divide my total purse (cell G2) among all the top placers (cell G3, which could be any number from 1 and up).

Simply dividing G2 by G3 would result equal shares for every top placer. What I want to do is divide G2 unequally so that the highest ranked person gets a little more than the second ranked person....and the lowest ranked person gets the least share. (Rankings are listed in column A).

So, if there are only a few placers (in G3), there would be a greater range from the amount awarded from one placement to the next. Whereas, if there were a lot of placers (in G3), there would be very small differences from the amount awarded from one person to the next.

Does this even sound possible to do? If so, I have one more wrench to throw in. What if there are ties for rankings...?

jaedmar said:

Although I don't think that is actually what I was trying to do, I did give it a shot, but it did not seem to work.

What I am trying to do is to divide my total purse (cell G2) among all the top placers (cell G3, which could be any number from 1 and up).

Simply dividing G2 by G3 would result equal shares for every top placer. What I want to do is divide G2 unequally so that the highest ranked person gets a little more than the second ranked person....and the lowest ranked person gets the least share. (Rankings are listed in column A).

So, if there are only a few placers (in G3), there would be a greater range from the amount awarded from one placement to the next. Whereas, if there were a lot of placers (in G3), there would be very small differences from the amount awarded from one person to the next.

Does this even sound possible to do? If so, I have one more wrench to throw in. What if there are ties for rankings...?

Click to expand...

That's exactly what I suggested does. I use points as the prize rather than a money prize but it's a simple matter to change the points to a dollar amount.

You should just have to adapt it to your layout.

If that is not what you want then post some sample data so we can see what you do want.

jaedmar,

When you say the highest ranked person gets a little more than the second ranked person, do you mean a distribution like the following?

Assume the purse goes to the top five finishers.
Top finisher gets 5 / (Sum of 1 to 5) = 5 / 15 * Purse
2nd finisher gets 4 / 15 * purse
3rd finisher gets 3 / 15 * purse
4th finisher gets 2 / 15 * purse
5th finisher gets 1 / 15 * purse
All others get zero.

If this is the distribution you want, and using the example data that Biff provided:

Joe gets 5/15 of the purse

Bill, Tom, and John are tied, so they each get an equal share of the sum of the 2nd, 3rd and 4th finishers purse; or 3/15 of the purse each.

How do you want to split the remaining 1/15 of the purse among Tim, Jim, and Loe? Do each of them get 1/3 of the 1/15, or 1/45 of the purse?

If the above distribution is correct; and you can describe how to pay Tim, Jim, and Loe; I may have a formula to calculate the payout.

Just out of curiousity, why should someone who scores the same as another get less just because they are "ranked" lower? Surely they played equally well on the day and deserve the same prize for their performance.

Anyway, I suppose if you want to bias a formula in favor of higher ranked players you can do it any way you like. They can get twice as much as lower ranked players. Or three times as much. Or all of it but a penny ... in other words, you make the rules here.

Edit: Actually, this leads me to surmise that it would be good to first state in words how the purse will be divided. Then find a formula that satisfies those requirements. You originally said the top ranked player gets the "greatest" portion. But "greatest" is not defined.

T. Valko said:

That's exactly what I suggested does. I use points as the prize rather than a money prize but it's a simple matter to change the points to a dollar amount.

You should just have to adapt it to your layout.

If that is not what you want then post some sample data so we can see what you do want.

Click to expand...

Hmmm...

Maybe I misunderstood what you want.

I assumed you already had the prize pool established. Like this, for example:

1st place gets $10,000
2nd place gets $8,500
3rd place gets $7000
4th place gets $5000
etc
etc

For what it's worth, I've tried to work this out once before and it's not quite as simple as it appears, mainly due to the number of players changing.
First point, if there are ties, say 3 people finish 2nd, then the prize money should be the sum of 2nd 3rd and 4th divided equally by 3. When prizemoney is involved the countback is not used, only the final score.
Secondly, the "usual" algorithm is the winner gets between 40 - 60 % of the purse, 2nd gets 70% of whats left, 3rd gets 70% of whats left after 1st and 2nd.....etc.
Problem is that this applies to a fixed no of prizes, say 10. If the prize list is increased or decreased, the algorithm goes Pearshaped.

And I guess, there is the challenge.

The 3 variables are:
1. How many prizes are on offer
2. What is the winning percentage ( normally small field winner gets higher %)
3. What is the total prize pool ( $10000 for 10 players will affect the algorithm much more than $1m for 100 players)

I will be watching closely.....and sending this query to our resident Einstein.

cheers guys.

...

H5 and down: =COUNTIF($D$5:$D$26, F5)

I5 and down: =IF(H5=0, "", SUMIF($D$5:$D$26, D5, $G$5:$G$26)/COUNTIF($D$5:$D$26, D5))

J5 and down: =IF(H5=0, "", H5*I5)