Formula for Prize Money

mikajake

New Member
Joined
Aug 4, 2002
Messages
25
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

New Member
Joined
Aug 4, 2002
Messages
25
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

New Member
Joined
Aug 4, 2002
Messages
25
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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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
ABCDEF
1TeamScoreRankTeamScoreRank
2Bushie281Bushie281
3Hoffa281Hoffa272
4Jim273Jim272
5Joe273Joe264
6Craig273Craig264
7Ian266Ian264
8John266John264
9Stephen266Stephen264
10
11TeamScoreRankTeamScoreRank
12Bushie281Bushie281
13Hoffa281Hoffa272
14Jim281Jim263
15Joe281Joe263
16Craig281Craig263
17Ian266Ian256
18John266John256
19Stephen266Stephen256
Sheet1


Regards
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,888
Messages
5,766,948
Members
425,389
Latest member
Naresha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top