Formula for Prize Money

mikajake · Feb 23, 2004

I have a spreadsheet that after ranking the players are given prize money. The prizes are 1st, 2nd, 3rd. The money is $100 for 1st, $75 for 2nd, $50 for 3rd.

If there are no ties for the three positions then no problem. but if there is a tie for any of the positions then, the money gets split amongst them. So for this example:

SUPER / BUSHIE 28
HOFFA 28
JIM 27
JOE 27
CRAIG / RICHIE 27

Super / Bushie should get the 1,2 prize money / 2 or $75 dollars each and the third prize is split amongst the three players.

So the display or printout is as follows:

SUPER / BUSHIE 28 $75.00
HOFFA 28 $75.00
JIM 27 $16.66
JOE 27 $16.66
CRAIG / RICHIE 27 $16.66

any help would be appreciated.

Ian Mac · Feb 23, 2004

Hello,

Your excmple does not make much sense in terms of what you asking.

Is SUPER / BUSHIE 1 person?
Also doesn't Hoffa share 1st place with SUPER / BUSHIE?
If you are sharing 1st and 2nd place between Hoffa and SUPER / BUSHIE then shouldn't the prize money be $175 / 2 not $150?

Please could you clarify your needs.

Regards,

mikajake · Feb 23, 2004

I apologize. Each is a separate team. Super/Bushie, Hoffa etc. The numbers 28, 27 are the points they received for the week. And you are correct first and second do add up to 175 so 87.50 each.

acw · Feb 23, 2004

Hi

Assuming that the data you have entered is in the range A1:B5, then enter the formula in C1 and copy down to C5.
=RANK(B1,$B$1:$B$5)
In D1 enter the formula
=VLOOKUP(C1,$E$1:$G$3,3) and copy down to D5
In E1 :E3 enter 1,2,3
In F1 enter =COUNTIF($C$1:$C$5,E1) and copy down to F3.
In G1 enter =SUM($I$1:OFFSET($I$1,F1-1,0))/F1
in G2 enter =SUM($I$2:OFFSET($I$2,F2-1,0))/F2
in G3 enter =SUM($I$3:OFFSET($I$3,F3-1,0))/F3
in I1:I3 enter 100,75,50

This should get you started. What happens if there are 3 or more entries that win, and no third has been assumed that the entire pool is divided amongst the winners.

Tony

mikajake · Feb 23, 2004

If there are more than one winner for any position, the money is spread accordingly. if there are 5 people tied for first and only one second place and one third, first is split 5 ways.

Ian Mac · Feb 23, 2004

mikajake said:
If there are more than one winner for any position, the money is spread accordingly. if there are 5 people tied for first and only one second place and one third, first is split 5 ways.

This doesn't go with what you have already said, if there are 5 1st placed people then 5 people should share $225 according to your first answers.

Could you give a few more senarios given the following information:

Export Test.dbf