Point Scoring Using Substituted Numbers

[Valik]

Hi Everyone,

I'm having trouble creating a spreadsheet that calculates points for
online racing simulation leagues.

To make it easy to input the data I was thinking of having a table
that contained the finishing positions and then excel could do the
rest.

The main problem I'm having is converting the table of finishing
positions into a points table. The required data is as follows.

Sprint Rounds constitute 2 races each worth 30 points
1st = 30
2nd = 28
3rd = 26
4th = 24
5th = 22
6th = 20
7th = 18
8th = 16
9th = 14
10th = 12
11th = 10
12th = 8
13th = 6
14th = 4
15th = 2

Enduro Rounds are 1 race only and therefore worth double points
1st = 60
2nd = 56
3rd = 52
4th = 48
5th = 44
6th = 40
7th = 36
8th = 32
9th = 28
10th = 24
11th = 20
12th = 16
13th = 12
14th = 8
15th = 4

You're worst (total) round will be dropped and the series champion
decided on their best 5 rounds.

In the long term I'm looking at making variables such as points
awarded, dropping worst round and use of sprint rounds, etc... easily
adjustable.

Thanks in advance

Nathan

 

[Tom Ogilvy]

if you created a table like

1 30
2 28
etc

then you can convert a place to points using Vlookup

(Or if the cell will contain 1st rather than 1, replace the 1 above with
1st, etc)

for the other types of decisions, there are probably formulas that could be
used, but you description is way to vague to suggest anything.

 

[Dana DeLouis]

Without any error checking, perhaps you could work a small equation into it.
Your rankings appear to be linear, so this is just a general idea.

Dim s, x, Sprint, Enduro
s = "5th"
x = Val(s) '5

Sprint = 2 * x - 32 '22
'or
Enduro = 4 * x - 64 '44