Spreadsheet to calculate payouts in tournaments????

[43fan]

I posted this in excel.worksheetfunctions as well, but thought I'd try here
too...

I'm trying to come up with something that would allow a breakdown of payouts
for a tournament, based on the number of entries.

Say I wanted to pay 1 in 4(pay one place for every 4 entries). I'd like to
be able to enter the number of entries, the amount going into the prize fund
for each entry, and the percentage being paid to first place, then let the
sheet calculate how many more places I'd pay, and distribute the remaining
amount among those places.

Any ideas?

Thanks!
Shawn


--
It's not just based on number of championships won. Richard Petty won
200 races and 7 Daytona 500s in his 30+ year driving career. He also has
the most top-5s (555), top-10s (712), poles (126), laps completed
(307,836), laps led (52,194), races led (599) and consecutive races won
(10 in 1967) of any driver in NASCAR history.

 

[Dick Kusleika]

Shawn

let the
sheet calculate how many more places I'd pay,

That's easy - the number of entries divided by 4, right?

and distribute the remaining
amount among those places.

Distribute how? If you have 40 entries, you will pay 10 places. If each
entry is $10 and you will pay $100 to the winner, then you have $300 for 9
places. Surely that won't be distributed evenly. One way to do it would be
to use the sum-of-the-years-digits like they did for depreciation in the old
days. Here's an example

Function Payout(ENumb As Long, EPrice As Double, _
WinPay As Double, Place As Long) As Double

Dim TotalPot As Double
Dim TotalPlaces As Long
Dim SOYD As Long
Dim i As Long

TotalPot = (ENumb * EPrice) - WinPay

'\ = integer division
TotalPlaces = (ENumb \ 4)

SOYD = 0
For i = 2 To TotalPlaces
SOYD = SOYD + i
Next i

If Place <= 1 Or Place > TotalPlaces Then
Payout = CVErr(xlErrValue)
Else
Payout = TotalPot * ((TotalPlaces - Place + 2) / SOYD)
End If

End Function

Enumb = number of entries
EPrice = cost per entry
winpay = amount paid to first place
Place = Place you want to compute