Nathaniel wrote:
I am creating a spreadsheet in Calc to track the drivers and teams in this
years Formula 1 championship.
At the moment it consists of 3 sheets with the main sheet being 18 cols by
25 rows. After each race I'm entering the finishing position of the drivers
from 1 to 8 as the points system only pays down to 8th place.
My question then is Can I use a formula to scan my race results sheet and
take the information from their and use it on both the drivers tally sheet
and the teams tally sheet? The catch being that I want the points for the
position to be added not the position the driver actually finished in.
To explain:
1st=10points
2nd=8
3rd=6
4th=5
5th=4
6th=3
7th=2
8th=1
So is there a formula that can get Calc to see the number 1 but actually add
10 to the cell on the other sheet and so on down the list??
I hope I have explained this correctly.
1. Set up two columns of "constant" values:
In a column, say Column A, put the numbers 1, 2, 3, 4, 5, 6, 7, 8, 9 -
the point-scoring positions (including 9th, see below)
In another column, say column B, put the corresponding points: 8, 6, 5,
4, 3, 2, 1, 0 (including zero for 9th place, see below)
Assume both sets start in row 1.
2. Then assume the actual positions of the drivers in the race are in
column C, also starting in row 1
3. Then the points scored for a driver are arrived at by the formula:
=LOOKUP(Cn;A$1:A$9;B$1:B$9) where "n" is the row number in which the
driver's position is entered
4. So if you have existing points in column D, the new points (after the
race) for the driver represented by
row 1 are given by =D1+LOOKUP(C1;A$1:A$9;B$1:B$9)
row 2 are given by =D2+LOOKUP(C2;A$1:A$9;B$1:B$9)
etc.
The "$" signs prevent Calc adjusting the row numbers as you copy the
formula down the "new points" column. You *want* Calc to adjust the row
number in column C, hence no $ sign. (In the jargon, the "$" converts
the reference from relative to absolute)
The reason for having *9* rows of scores is that this way Calc will
automatically assign a point score of zero to any driver coming below
8th - the LOOKUP function returns the last entry in the specified list
if it can't find what it's looking for.
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
