# Formula for Prize Money

#### mikajake

##### New Member
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Ian Mac

##### MrExcel MVP
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?

Regards,

#### mikajake

##### New Member
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
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
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
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

Replies
8
Views
138
Replies
6
Views
587
Replies
5
Views
179
Replies
6
Views
5K
Replies
4
Views
1K

1,186,368
Messages
5,957,455
Members
438,306
Latest member
Crystal_Blue

### 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.

### Which adblocker are you using?

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

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