On Mon, 17 Oct 2005 16:59:02 -0700, "Cabo Wabo Jim" <Cabo Wabo
wrote:

I have a spread sheet in which I enter players names and the place they
finished for a certain event. I would like the sheet to calculate their
points according to where they finish and assign it to them along with a
formula that calculates a bonus point value according to the number of
players in a certain event.
1st place-450 pts + 30% bonus
2nd place-300 pts + 20% bonus
3rd place-180 pts + 12% bonus
4th place-120pts + 8% bonus
5th place-105 pts + 7% bonus
6th place 90 pts + 6% bonus
7th place 75 pts + 5% bonus
8th place 60pts + 4% bonus
9th-16th place 15 pts + 1% bonus

16th place 15 points

Bonus point formula is (# players X 5)x %

Any help would be appreciated

I found it easiest to use a UDF to calculate the total scores.

This assumes that the players are listed in a column, and each event is also in
a column, and that each players "place" is entered into the data table area.
So the worksheet looks like:


Event 1 2 3 4 5 ...
Scores
Player1 651.3 3 1 16 95
Player2 1073.9 2 1 5 14 7
Player3 937.8 1 5 4 3 25
....

To enter this UDF, <alt<F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

Change the parameters for FirstRow and NumPlayers as required.

FirstRow is the first row in which Places are entered; NumPlayers is the
maximum number of players that might be registered. It really just sets an
area to count for any entries.

You might want to lightly fill the area so that you don't put extraneous data
in by mistake, which would distort the scores.

In your Scores column, enter the formula:

=totalscore(ScoreRng)

where ScoreRng is the row where scores are entered for this player. In the
example above it might be =totalscore(C3:H3)

===============================
Option Explicit
Function TotalScore(Place As Range) As Double
Application.Volatile
Dim c As Range
Const FirstRow As Long = 3
Const NumPlayers As Long = 100
Dim Points
Dim PerCents

Points = Array(0, 450, 300, 180, 120, 105, 90, 75, 60, _
15, 15, 15, 15, 15, 15, 15, 15)

PerCents = Array(0, 0.3, 0.2, 0.12, 0.08, 0.07, 0.06, 0.05, _
0.04, 0.01, 0.01, 0.01, 0.01, 0.01, 0.01, 0.01, 0)

For Each c In Place
If c.Value <= 16 Then
TotalScore = TotalScore + Points(c.Value) + 5 * PerCents(c.Value) * _
Application.WorksheetFunction.Count(Range(Cells(Fi rstRow, c.Column), _
Cells(FirstRow + NumPlayers - 1, c.Column)))
End If
Next c

End Function
===============================


--ron