Points Ranking Formulas

[Guest]

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]

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

 

[Guest]

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?

 

[Guest]

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?

 

[Ron Rosenfeld]

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

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(FirstRow, c.Column), _
Cells(FirstRow + NumPlayers - 1, c.Column)))
End If
Next c

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


--ron

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Max]

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

 

[Guest]

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

Ron,
Thanks again for your help, I just get a bit confused with all the functions
and how to exactly set them up. I'm still a novice at this, I am not sure
how to set up a summary sheet, seperate worksheets/tables or use the VLOOKUP
formula. But I will work with the information you provided, which makes
sense to me, and try to understand it as it relates to my specific situation
to assure I am setting up the functions correctly as to have no errors.

 

[Max]

Here's the set-up in the enhanced sample file

In sheet: Lower Score is Better (eg Golf)
---------------------
The set-up in cols A to M (data/formulas in row2 down):

A Name
B Score
C TB1
D Name
E Score
F Rank
G TB2
H Name
I Placing
J Points
K Bonus
L Bonus Points
M Total

Cols A & B are for input of player names & scores
Cols C to G are helpers
Cols H to M are the results

In C2: =IF(B2="","",B2+ROW()/10^10)

In D2:
=IF(ISERROR(SMALL($C:$C,ROWS($A$1:A1))),"",
INDEX(A:A,MATCH(SMALL($C:$C,ROWS($A$1:A1)),$C:$C,0)))
D2 is copied to E2

In F2: =IF(E2="","",RANK(E2,OFFSET($E$2,,,COUNTA(E:E)-1),1))
In G2: =IF(F2="","",F2+ROW()/10^10)

In H2:
=IF(ISERROR(SMALL($G:$G,ROWS($A$1:A1))),"",
INDEX(D,MATCH(SMALL($G:$G,ROWS($A$1:A1)),$G:$G,0)))

In I2:
=IF(ISERROR(SMALL($G:$G,ROWS($A$1:B1))),"",
INDEX(F:F,MATCH(SMALL($G:$G,ROWS($A$1:B1)),$G:$G,0)))

In J2:
=IF(E2="","",SUMIF(OFFSET($I$2,,,COUNTA(B:B)-1),$I2,Points)
/COUNTIF(OFFSET($I$2,,,COUNTA(B:B)-1),$I2))

In K2:
=IF(E2="","",SUMIF(OFFSET($I$2,,,COUNTA(B:B)-1),$I2,Bonus)
/COUNTIF(OFFSET($I$2,,,COUNTA(B:B)-1),$I2))

In L2: =IF(E2="","",(COUNTA(B:B)-1)*5*K2)
In M2: =IF(E2="","",SUM(J2,L2))

D2:M2 selected and filled down

In sheet: Higher Score is Better (Others)
---------------------
Exactly the same set-up & suite of formulas in cols C to M,
except for a small change made to col F's formulas:
In F2: =IF(E2="","",RANK(E2,OFFSET($E$2,,,COUNTA(E:E)-1)))
F2 copied down

In sheet: Point n Bonus Structure
---------------
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

 

[Max]

Cabo, I've since responded to your feedback in the other branch, with an
enhanced sample for you to play with (.. spent the better part of the early
morning over here getting that up, gawd!<g>..). Spare some time for a
look-see there as well ..

 

[Guest]

Max,
I appreciate you help and I have played with your solution, but I 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?

If I use a seperate column to enter the number of players in an event how
can I incoprporate that into the formulas?

Thanks again!

 

[Max]

Max,
I appreciate you help and I have played with your solution, but I 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?

These were your earlier comments to the 1st sample. I've since provided a
new, 2nd sample file (new link) in my response to your reply in the other
branch. Pl confirm that we're talking about the 2nd file.

In case it was missed earlier,
here's the link to the 2nd sample I posted over there:
http://www.savefile.com/files/4835997
File: 1_Points Ranking Formulas_Cabo_wksht.xls

Which sort of game is your interest ?

I've provided a choice of 2 for you to choose, in the 2 sheets:
Lower Score is Better (eg Golf)
Higher Score is Better (Others)

If I use a seperate column to enter the number of players in an event how
can I incorporate that into the formulas?

You don't even need to do that. The relevant formulas will implicitly
auto-calc the number of players from col B ("Score" col ) in either of the 2
sheets mentioned above in the 2nd sample file. The list of players in col A
can be left "permanently" there, once entered. Just clear (or leave empty)
the scores in col B for those players who are not participating in that
round.

Remember that the results are read-off / displayed
within cols H to M in either of the 2 sheets:
Lower Score is Better (eg Golf)
Higher Score is Better (Others)

 

[Guest]

Thanks Max, I missed that earlier, I will take a look at it ASAP.
Jim

 

[Max]

Oops, forgot Sheet2 was renamed as: Point n Bonus Structure

Lines:

Name Refers To

should read as:

Name Refers To

 

[Max]

... I missed that earlier ..

Aha! guessed as much <g>

 

[Guest]

Max,
Sorry I have been out of town for meetings and did not have time to work on
the spread sheet. I have a layout of what I want but can't seem to get it to
work, if you'd like to e-mail me at (e-mail address removed) I'd like to send
you a copy of what I have?

 

[Max]

I've got your mail & file, Jim
Will follow up here ..