Points Ranking Formulas

[Cabo Wabo Jim]

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cabo Wabo Jim" <Cabo Wabo Jim@discussions.microsoft.com> wrote in message
news:78A7E539-91F1-4622-A38E-107AF8E73D80@microsoft.com...
> 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

[Cabo Wabo Jim]

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 Jim@discussions.microsoft.com> wrote in message
> news:78A7E539-91F1-4622-A38E-107AF8E73D80@microsoft.com...
> > 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
>
>
>

[Cabo Wabo Jim]

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 Jim@discussions.microsoft.com> wrote in message
> news:78A7E539-91F1-4622-A38E-107AF8E73D80@microsoft.com...
> > 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
>
>
>

[Ron Rosenfeld]

On Mon, 17 Oct 2005 16:59:02 -0700, "Cabo Wabo Jim" <Cabo Wabo
Jim@discussions.microsoft.com> 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(FirstRow, c.Column), _
Cells(FirstRow + NumPlayers - 1, c.Column)))
End If
Next c

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


--ron

[Cabo Wabo Jim]

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
> Jim@discussions.microsoft.com> 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(FirstRow, c.Column), _
> Cells(FirstRow + NumPlayers - 1, c.Column)))
> End If
> Next c
>
> End Function
> ===============================
>
>
> --ron
>

[Ron Rosenfeld]

On Tue, 18 Oct 2005 12:46:05 -0700, "Cabo Wabo Jim"
<CaboWaboJim@discussions.microsoft.com> 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

[Cabo Wabo Jim]

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"
> <CaboWaboJim@discussions.microsoft.com> 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 Rosenfeld]

On Tue, 18 Oct 2005 14:16:15 -0700, "Cabo Wabo Jim"
<CaboWaboJim@discussions.microsoft.com> 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

[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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cabo Wabo Jim" <CaboWaboJim@discussions.microsoft.com> wrote in message
news:85233B21-EB00-4DCE-9BBC-76E68C154609@microsoft.com...
> 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" <CaboWaboJim@discussions.microsoft.com> wrote in message
news:E44A1658-0EC0-49CE-8592-C95B645AD7C7@microsoft.com...
> 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?

[Cabo Wabo Jim]

"Ron Rosenfeld" wrote:

> On Tue, 18 Oct 2005 14:16:15 -0700, "Cabo Wabo Jim"
> <CaboWaboJim@discussions.microsoft.com> 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
>
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: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
--------------------
Bonus =Sheet2!$C$2:$C$17
Points =Sheet2!$B$2:$B$17

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Cabo Wabo Jim]

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" wrote:

> 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 ..
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

[Max]

"Cabo Wabo Jim" wrote
> 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)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Cabo Wabo Jim]

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

"Max" wrote:

> "Cabo Wabo Jim" wrote
> > 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)
>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

[Max]

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

Lines:

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

should read as:

Name Refers To
---------------------
Bonus ='Point n Bonus Structure'!$C$2:$C$17
Points ='Point n Bonus Structure'!$B$2:$B$17
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

"Cabo Wabo Jim" wrote:
> ... I missed that earlier ..
Aha! guessed as much <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Cabo Wabo Jim]

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 jimschaller@cfl.rr.com I'd like to send
you a copy of what I have?

"Max" wrote:

> "Cabo Wabo Jim" wrote:
> > ... I missed that earlier ..
> Aha! guessed as much <g>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

[Cabo Wabo Jim]

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 jimschaller@cfl.rr.com I'd like to send
you a copy of what I have?

"Max" wrote:

> "Cabo Wabo Jim" wrote:
> > ... I missed that earlier ..
> Aha! guessed as much <g>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

[Max]

I've got your mail & file, Jim
Will follow up here ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

Easier to start all over, Jim <g>. Now that I see your detailed set-up,
here's the formulas you need to compute the points. I've implemented these
in your file and will send it back to you.

Placed in F7:
=IF(OR(E7="",E$4=""),"",INDEX(Sheet2!$B:$B,MATCH(E7,Sheet2!$A:$A,0))
+E$4*5*INDEX(Sheet2!$C:$C,MATCH(E7,Sheet2!$A:$A,0)))

Copied F7, then with CTRL key held down,
selected cells H7,J7,L7,N7,P7,R7,T7,V7,X7
Then right-click > paste special > formulas > OK

This pastes the same formula adjusted relatively where required into all the
required cells. Then just copy the formulae down the respective columns.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Cabo Wabo Jim]

Thanks Max,

I think I'm starting to get it now. It takes awhile to understand how the
formulas work.

You did a great job on this!

Cabo Wabo Jim

"Max" wrote:

> Easier to start all over, Jim <g>. Now that I see your detailed set-up,
> here's the formulas you need to compute the points. I've implemented these
> in your file and will send it back to you.
>
> Placed in F7:
> =IF(OR(E7="",E$4=""),"",INDEX(Sheet2!$B:$B,MATCH(E7,Sheet2!$A:$A,0))
> +E$4*5*INDEX(Sheet2!$C:$C,MATCH(E7,Sheet2!$A:$A,0)))
>
> Copied F7, then with CTRL key held down,
> selected cells H7,J7,L7,N7,P7,R7,T7,V7,X7
> Then right-click > paste special > formulas > OK
>
> This pastes the same formula adjusted relatively where required into all the
> required cells. Then just copy the formulae down the respective columns.
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

[Max]

You're welcome, Jim !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cabo Wabo Jim" <CaboWaboJim@discussions.microsoft.com> wrote in message
news:D33B8DCB-4DB0-46CD-89B1-95C096FED25C@microsoft.com...
> Thanks Max,
>
> I think I'm starting to get it now. It takes awhile to understand how the
> formulas work.
>
> You did a great job on this!
>
> Cabo Wabo Jim