Points Ranking Formulas

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

Here's one crack at it ..

Sample file with the construct at:
http://www.savefile.com/files/4535198
Points Ranking Formulas_Cabo_wksht.xls

In Sheet2
--------
Table below is in A1:C17

Placing Points Bonus
1 450 30%
2 300 20%
3 180 12%
4 120 8%
....
15 15 1%
16 15 1%

2 defined names are created via: Insert Name Define

Name Refers To
--------------------
Bonus =Sheet2!$C$2:$C$17
Points =Sheet2!$B$2:$B$17

In Sheet1
-------
Table in cols A to F comprises:

Name-Placing-Points-Bonus-Bonus Points-Total
Play1 1 450 30.0% 24 474.0
Play2 2 240 16.0% 12.8 252.8
Play3 2 240 16.0% 12.8 252.8
Play4 4 120 8.0% 6.4 126.4
etc

where

Name col: Assumed maximum of 16 players
(= # of placings in Sheet1)

Placing col: Placings are manually input and assumed to include possibility
of ties. Tied placings are assumed treated in the same way as RANK(...). For
example, if the placing 2 appears twice, placing 3 would be skipped. And so
on.

Formulas placed in cols C to F:

In C2:
=SUMIF($B$2:$B$17,$B2,Points)/COUNTIF($B$2:$B$17,$B2)

In D2:
=SUMIF($B$2:$B$17,$B2,Bonus)/COUNTIF($B$2:$B$17,$B2)

In E2: =(COUNTA(A:A)-1)*5*D2
In F2: =SUM(C2,E2)

C2:F2 selected and copied down to F17

Note that Points and Bonus % are assumed
pro-rata apportioned in the event of ties
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cabo Wabo Jim" Cabo Wabo wrote in message
...
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

Max,
I appreciate it, it seems to work expcept one thing. I may have up to 100
players but only 16 getting points. Is there anywhere I can input the number
of players as to calculate the bonus in correlation with that number?

"Max" wrote:

Here's one crack at it ..

Sample file with the construct at:
http://www.savefile.com/files/4535198
Points Ranking Formulas_Cabo_wksht.xls

In Sheet2
--------
Table below is in A1:C17

Placing Points Bonus
1 450 30%
2 300 20%
3 180 12%
4 120 8%
....
15 15 1%
16 15 1%

2 defined names are created via: Insert Name Define

Name Refers To
--------------------
Bonus =Sheet2!$C$2:$C$17
Points =Sheet2!$B$2:$B$17

In Sheet1
-------
Table in cols A to F comprises:

Name-Placing-Points-Bonus-Bonus Points-Total
Play1 1 450 30.0% 24 474.0
Play2 2 240 16.0% 12.8 252.8
Play3 2 240 16.0% 12.8 252.8
Play4 4 120 8.0% 6.4 126.4
etc

where

Name col: Assumed maximum of 16 players
(= # of placings in Sheet1)

Placing col: Placings are manually input and assumed to include possibility
of ties. Tied placings are assumed treated in the same way as RANK(...). For
example, if the placing 2 appears twice, placing 3 would be skipped. And so
on.

Formulas placed in cols C to F:

In C2:
=SUMIF($B$2:$B$17,$B2,Points)/COUNTIF($B$2:$B$17,$B2)

In D2:
=SUMIF($B$2:$B$17,$B2,Bonus)/COUNTIF($B$2:$B$17,$B2)

In E2: =(COUNTA(A:A)-1)*5*D2
In F2: =SUM(C2,E2)

C2:F2 selected and copied down to F17

Note that Points and Bonus % are assumed
pro-rata apportioned in the event of ties
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cabo Wabo Jim" Cabo Wabo wrote in message
...
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

Max,
I also find that when I randomly enter the player place finish it does not
matter if I put in 1 or 16 it calculates the points in the order I enter
them, the first entry gets 1st place points the second 2nd place and so on.
If I enter place number 5 first it gets first place points?


"Max" wrote:

Here's one crack at it ..

Sample file with the construct at:
http://www.savefile.com/files/4535198
Points Ranking Formulas_Cabo_wksht.xls

In Sheet2
--------
Table below is in A1:C17

Placing Points Bonus
1 450 30%
2 300 20%
3 180 12%
4 120 8%
....
15 15 1%
16 15 1%

2 defined names are created via: Insert Name Define

Name Refers To
--------------------
Bonus =Sheet2!$C$2:$C$17
Points =Sheet2!$B$2:$B$17

In Sheet1
-------
Table in cols A to F comprises:

Name-Placing-Points-Bonus-Bonus Points-Total
Play1 1 450 30.0% 24 474.0
Play2 2 240 16.0% 12.8 252.8
Play3 2 240 16.0% 12.8 252.8
Play4 4 120 8.0% 6.4 126.4
etc

where

Name col: Assumed maximum of 16 players
(= # of placings in Sheet1)

Placing col: Placings are manually input and assumed to include possibility
of ties. Tied placings are assumed treated in the same way as RANK(...). For
example, if the placing 2 appears twice, placing 3 would be skipped. And so
on.

Formulas placed in cols C to F:

In C2:
=SUMIF($B$2:$B$17,$B2,Points)/COUNTIF($B$2:$B$17,$B2)

In D2:
=SUMIF($B$2:$B$17,$B2,Bonus)/COUNTIF($B$2:$B$17,$B2)

In E2: =(COUNTA(A:A)-1)*5*D2
In F2: =SUM(C2,E2)

C2:F2 selected and copied down to F17

Note that Points and Bonus % are assumed
pro-rata apportioned in the event of ties
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cabo Wabo Jim" Cabo Wabo wrote in message
...
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

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, altF11 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

Ron,
That works great but same problem.

If the number of players for each event varies that varies the bonus point
structure for each event. I need to total up about 10 seperate events per
month into one scoring system and ranking.

Jim

"Ron Rosenfeld" wrote:

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, altF11 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

On Tue, 18 Oct 2005 12:46:05 -0700, "Cabo Wabo Jim"
wrote:

Ron,
That works great but same problem.

If the number of players for each event varies that varies the bonus point
structure for each event. I need to total up about 10 seperate events per
month into one scoring system and ranking.

Jim

Either I did not understand what you wrote, OR you are not using the function
as it was designed.

If, as I wrote, "each players 'place' is entered into the data table area" then
every player that played will be counted. Of course, only those that placed at
level 1-16 will have a score generated.

Perhaps if you post some real data that is not giving the correct results, it
would be helpful.
--ron

Ron,
I'm not sure you know what I mean. For example the first event had 48
players with only 16 getting points, but the bonus is calculated by mutipling
the number of players in the event (48) by 5 and multipling that number by
the percentage of bonus they finished. So a person finishing in second would
get 300 points and a 20% bonus ((48 x 5)x.20)
PLACE LAST FIRST MEMBER # PL PTS Bonus% Bonus POINTS

1st Schaller Jim 20200005277 16 450 0.3 3 453
2nd 2 300 0.2 2 302
3rd 3 180 0.12 1.2 181.2
4th 4 120 0.08 0.8 120.8
5th 5 105 0.07 0.7 105.7
6th 6 90 0.06 0.6 90.6
7th 7 75 0.05 0.5 75.5
8th 6 90 0.04 0.4 90.4
9th 9 15 0.01 0.1 15.1
10th 10 15 0.01 0.1 15.1
11th 12 15 0.01 0.1 15.1


"Ron Rosenfeld" wrote:

On Tue, 18 Oct 2005 12:46:05 -0700, "Cabo Wabo Jim"
wrote:

Ron,
That works great but same problem.

If the number of players for each event varies that varies the bonus point
structure for each event. I need to total up about 10 seperate events per
month into one scoring system and ranking.

Jim

Either I did not understand what you wrote, OR you are not using the function
as it was designed.

If, as I wrote, "each players 'place' is entered into the data table area" then
every player that played will be counted. Of course, only those that placed at
level 1-16 will have a score generated.

Perhaps if you post some real data that is not giving the correct results, it
would be helpful.
--ron

On Tue, 18 Oct 2005 14:16:15 -0700, "Cabo Wabo Jim"
wrote:

Ron,
I'm not sure you know what I mean. For example the first event had 48
players with only 16 getting points, but the bonus is calculated by mutipling
the number of players in the event (48) by 5 and multipling that number by
the percentage of bonus they finished. So a person finishing in second would
get 300 points and a 20% bonus ((48 x 5)x.20)
PLACE LAST FIRST MEMBER # PL PTS Bonus% Bonus POINTS

1st Schaller Jim 20200005277 16 450 0.3 3 453
2nd 2 300 0.2 2 302
3rd 3 180 0.12 1.2 181.2
4th 4 120 0.08 0.8 120.8
5th 5 105 0.07 0.7 105.7
6th 6 90 0.06 0.6 90.6
7th 7 75 0.05 0.5 75.5
8th 6 90 0.04 0.4 90.4
9th 9 15 0.01 0.1 15.1
10th 10 15 0.01 0.1 15.1
11th 12 15 0.01 0.1 15.1



From what you write, it seems I understood exactly what you want. However, in
the above example you show a worksheet set up entirely different from the way I
showed you to do it in my post.

Had you set it up and entered data the way I had described, then the formula
would have done exactly what you requested. Because EACH player that
participated would have had a finishing number; the first 16 would have gotten
scores, but the bonus would be computed based on the total number of players,
who would be identified because they would all have some rank.

The function I wrote can be easily modified to handle other similar situations.

However, if you are going to set up separate worksheets (or tables) for each
event as in the example you give above, there's really no need for anything
fancy.

All you need to do is enter in some cell the number of participants for each
event. Name it, for example, NumPlayers, and then use the formula:

=PTS+5*NumPlayers*BonusPerCent

to compute the score for each player.

You could then have a summary sheet where you added together VLOOKUP formula
results for each sheet, using the player number as the lookup_value.


--ron

Try this enhanced sample file, which addresses both your questions. I've
provided 2 sheets depending on whether lower scores are better (eg: golf),
or higher scores are better (eg: almost all games other than golf g). You
just need to input/maintain the names and scores into cols A and B (Just
clear away the dummy data). The final results will auto-compute in cols H to
M. Cols C to G are helper cols (can be hidden away). See whether the sample
better suits your needs.

http://www.savefile.com/files/4835997
1_Points Ranking Formulas_Cabo_wksht.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cabo Wabo Jim" wrote in message
...
Max,
I also find that when I randomly enter the player place finish it does not
matter if I put in 1 or 16 it calculates the points in the order I enter
them, the first entry gets 1st place points the second 2nd place and so
on.
If I enter place number 5 first it gets first place points?

"Cabo Wabo Jim" wrote in message
...
Max,
I appreciate it, it seems to work expcept one thing. I may have up to 100
players but only 16 getting points. Is there anywhere I can input the
number
of players as to calculate the bonus in correlation with that number?