Help with determining prize payout.

[capsicum]

I run a small American football pool with 23 members. Each person makes their picks each week, and puts $1 in the pot for each loss. At the end of the season, we collect the money and divide it up, with 33.34% going towards a Super Bowl party for all players. The remaining 66.66% is divided up as follows:

1st place - 50%
2nd place - 30%
3rd place - 10%
4th place - 6%
5th place - 4%

In the event of a tie, we are using the golf rules, i.e. if two people tie for second, the second and third place amounts are combined and split in half, thus eliminating the 3rd place payout.

What I am looking for is some way to show what each payout will be after each weeks games.

You can see the html version of my spreadsheet at www.leftcoastsiesta.com/fb09.htm

If you need any more information, let me know. I appreciate any help that I can get. Thanks!

 

[jbeaucaire]

Rather than trying to piece it into the individuals' names, I would just provide a "chart" players could refer to. This chart would show the current pool total and how that breaks up into individual payouts as well as what the payout would be for ties. Then they can find themselves in the weekly rankings. It gives them something to "lookup"...actually part of the fun.

Excel Workbook
	A	B	C	D	E	F
1	POOL	$1,150.00
2		PAYOUTS			TIES
3	Party	$ 383.41	33.34%		1st/2nd	$ 306.64
4					2nd/3rd	$ 153.32
5					3rd/4th	$ 61.33
6	1st	$ 383.30	50%		4th/5th	$ 38.33
7	2nd	$ 229.98	30%
8	3rd	$ 76.66	10%		1st/2nd/3rd	$ 229.98
9	4th	$ 46.00	6%		2nd/3rd/4th	$ 117.54
10	5th	$ 30.66	4%		3rd/4th/5th	$ 51.11
Sheet4

 

[Andrew Fergus]

I have looked at your spreadsheet and I think your layout is getting in the way of what you are trying to do. Assuming you have summarised the players results like this:

   A                                        B      C        
19 Summary of Players and Pot Contributions                 
20 Player                                   Pot    Position 
21 1                                        $32.00 1        
22 2                                        $32.00 1        
23 3                                        $36.00 8        
24 4                                        $32.00 1        
25 5                                        $50.00 9        
26 6                                        $35.00 4        
27 7                                        $35.00 4        
28 8                                        $35.00 4        
29 9                                        $35.00 4        
30 10                                       $90.00 10       
Sheet3
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
C21:C30 =RANK(B21,$B$21:$B$30,1)
[Table-It] version 09 by Erik Van Geit

Then you can use something like this, which will accommodate multiple people getting a tie, and also multiple ties (eg 3 people come first and 4 people come 4th).

   A                B       C       D           E        F       G  H       I  J  K  L  
 1 Total Prize Pot                                                                      
 2 Pot                      $412.00                                                     
 3 Party            33%     $137.33                                                     
 4 Prizes                   $274.67                                                     
 5                                                                                      
 6 Prize Allocation                                                 Winners             
 7 Position         Percent Count   Original    Adjusted Payout     1       2  3  4  5  
 8 1                50%     3       $137.34     $247.21  $82.40     1       2  4        
 9 2                30%     0       $82.40      $0.00    $0.00                          
10 3                10%     0       $27.47      $0.00    $0.00                          
11 4                6%      4       $16.48      $27.47   $6.87      6       7  8  9     
12 5                4%      0       $10.99      $0.00    $0.00                          
13 TOTAL            100%    7       leave blank $274.68  $274.68                        
14                                  leave blank                                         
15                                  leave blank                                         
16                                  leave blank                                         
Sheet3
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
B13:C13 =SUM(B8:B12)
C3      =ROUND(C2*B3,2)
C4      =C2-C3
C8:C12  =COUNTIF($C$21:$C$30,A8)
D8:D12  =ROUND($C$4*B8,2)
E8:E12  =IF(C8>0,SUM(OFFSET(D8,0,0,C8,1)),0)
E13     =SUM(E8:E12)
F8:F12  =IF(C8>0,ROUND(E8/C8,2),0)
F13     =SUMPRODUCT(F8:F12,C8:C12)
H8:L12  {=IF(H$7< =$C8,INDEX($A$21:$A$30,SMALL(IF($C$21:$C$30=$A8,ROW($A$21:$A$30)-ROW($A$20)),H$7)),"")}
{=formula}:
 select first cell
 enter formula without {}
 confirm with Control-Shift-Enter
 then copy down & across
[Table-It] version 09 by Erik Van Geit

Please note the array formula in cells H8:L12. Apologies for the slightly crooked layout.....but it seems to work.

Andrew

 

[capsicum]

Thank you both very much. Worked great!!! I really appreciate your help.

 

[capsicum]

Actually, Andrew... with the array formulas... what do I have to change to have each player listed in the tie? I keep getting the same return.

 

[Andrew Fergus]

Have you got the values 1 to 5 in cells H7:L7?

The key to returning multiple player are these parts:
=IF(H$7< =$C8
This part looks to column C for the number of winners at that level (ie there were three winners at this level per my example above), and row H contains the sequence of winners (H7 = 1 so I'm looking for the 1st winner at this level and so).

And the other crucial part is the SMALL function, in particular the reference to H7. Check you have these parts working correctly.

If you get stuck then post your formula.

Andrew

 

[capsicum]

Thank you. I played around with it for a while, and finally got it to work. I appreciate all of your help.

Have a great day!
Jeff

 

[Andrew Fergus]

You're welcome. That array formula is essentially a formula for returning the nth match (ie first match, second match, third match etc.) and is a really useful technique. If you have the time, and a small enough array, then use the formula evaluator (see menu option Tools > Formula Auditing > Evaluate Formula) to see how the SMALL function gets the appropriate match. It's worth remembering this technique.

Cheers
Andrew

P.S. A belated welcome to MrExcel!

 

[capsicum]

Andrew,

I know it has been a long time since you helped me with my problem, but I was hoping to find you for a little more assistance.

The solution you gave me above worked perfectly last year, but this year the rules are changing a little bit for the payouts....

In the previous year, it worked like this: $1 for each loss. The average losses per person at the end of the year were around $89. So, the fifth place person won $55 before subtracting for their losses.

This year, I would like to work it like this: As we are up to 31 players, paying to 7th place, and the money will be larger, I want to make it so that none of the placing players owe money. The percentages will be 29 for 1st, 24 for 2nd, 19 3rd, 14 4th, 9 5th, 5 6th, and 0 for 7th. So, what I need help with is getting the spreadsheet to figure out what the 0% for 7th place amount is, to build the percentages for the higher places. If total pot is $1840 and the 7th place guy loses $86, that would mean that 7th place wins $86, and 6th wins $88 ($1840-$86*5%) and so on.
Does this make sense?
I tried to figure out something like this last year, to figure the (much easier) payout amounts from the total accumulative losses.

I find this difficult to describe, and even harder to solve. Any help would be greatly appreciated. Let me know if there is any further information that you need.
Thank you in advance.
Jeff

 

[Andrew Fergus]

Hello again

I think we can get this to work and I think I am understanding this.

We count up the number of winners in 7th place or less (it could be more than 7 given 3 people could come 7th) and we pay back each player the amount the 7th player contributed, plus their % of what's left. See below:

Raw Data (limited to 30 rows by Table-It)

   B      C    D        E       F        
 1 Player Pot  Position Payout? Payout $ 
 2 P1     $89  21       FALSE   $0.00    
 3 P2     $100 25       FALSE   $0.00    
 4 P3     $51  4        TRUE    $302.50  
 5 P4     $65  10       FALSE   $0.00    
 6 P5     $65  10       FALSE   $0.00    
 7 P6     $87  19       FALSE   $0.00    
 8 P7     $120 29       FALSE   $0.00    
 9 P8     $52  5        TRUE    $178.25  
10 P9     $52  5        TRUE    $178.25  
11 P10    $61  9        FALSE   $0.00    
12 P11    $98  23       FALSE   $0.00    
13 P12    $66  12       FALSE   $0.00    
14 P13    $67  13       FALSE   $0.00    
15 P14    $54  7        TRUE    $54.00   
16 P15    $79  16       FALSE   $0.00    
17 P16    $54  7        TRUE    $54.00   
18 P17    $70  15       FALSE   $0.00    
19 P18    $50  3        TRUE    $391.25  
20 P19    $99  24       FALSE   $0.00    
21 P20    $44  1        TRUE    $524.38  
22 P21    $111 27       FALSE   $0.00    
23 P22    $68  14       FALSE   $0.00    
24 P23    $90  22       FALSE   $0.00    
25 P24    $44  1        TRUE    $524.38  
26 P25    $113 28       FALSE   $0.00    
27 P26    $110 26       FALSE   $0.00    
28 P27    $88  20       FALSE   $0.00    
29 P28    $81  18       FALSE   $0.00    
30 P29    $79  16       FALSE   $0.00    
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
D2:D30  =RANK(C2,$C$2:$C$30,-1)
E2:E30  =D2< 8
F2:F30  =E2*VLOOKUP(D2,$K$2:$Q$8,7)
[Table-It] version 09 by Erik Van Geit

Then a summary of the guaranteed minimums and what is left to be divvied up:

  H         I      
1 Summary          
2                  
3 Total Pot $2,207 
4 Lowest    $54    
5 Winners   8      
6 Minimums  $432   
7 Pot left  $1,775 
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
I3    =SUM(C:C)
I4    {=MAX(IF(D2:D30< =7,C2:C30,0))}°
I5    =COUNTIF(D2:D30,"< =7")
I6    =I4*I5
I7    =I3-I6
{=formula}°:
 select entire range
 enter formula without {}
 confirm with Control-Shift-Enter
[Table-It] version 09 by Erik Van Geit

Notice the array formula in cell I4.

Then the workings part:

  K        L      M     N       O       P       Q         R      S       
1 Position Payout Count Minimum Pot     Amended Payout    Losses Profit  
2 1        29%    2     $54.00  $514.75 $940.75 $524.38   $44.00 $480.38 
3 2        24%    0     $54.00  $426.00 $0.00   $0.00     $0.00  $0.00   
4 3        19%    1     $54.00  $337.25 $337.25 $391.25   $50.00 $341.25 
5 4        14%    1     $54.00  $248.50 $248.50 $302.50   $51.00 $251.50 
6 5        9%     2     $54.00  $159.75 $248.50 $178.25   $52.00 $126.25 
7 6        5%     0     $54.00  $88.75  $0.00   $0.00     $0.00  $0.00   
8 7        0%     2     $54.00  $0.00   $0.00   $54.00    $54.00 $0.00   
9 TOTAL                                         $2,207.00                
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
M2:M8 =COUNTIF($D$2:$D$30,K2)
N2:N8 =$I$4
O2:O8 =ROUND($I$7*L2,2)
P2:P8 =IF(M2=0,0,SUM(OFFSET(O2,0,0,M2,1)))
Q2:Q8 =IF(M2=0,0,N2+P2/M2)
Q9    =SUMPRODUCT($M$2:$M$8,Q2:Q8)
R2:R8 =IF(M2=0,0,INDEX($C$2:$C$30,MATCH(K2,$D$2:$D$30,0)))
S2:S8 =Q2-R2
[Table-It] version 09 by Erik Van Geit

I trust this helps. It is pretty intuitive and it has been built per my explanation at the beginnning. If you aren't sure of anything please ask.

Andrew