At 05:40 08/08/2007 +0800, Nathaniel Seventy-Three 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.
I understand you to say that you enter just the competitors who
finish in point-scoring positions - in other words, that you don't
need to consider positions beyond eighth. If not, you may have to
modify things a little to cope properly with the zero points scored
for these positions.
There are various ways of doing what you need. Two obvious ones are
to use an array or a formula.
1. Using an array:
Somewhere convenient, create a column of the points values, from 10
down to 1. (There is no need to list the corresponding
positions.) Suppose these values - an "array" - are in P1 to
P8. Then use the INDEX function to pick up the points value
corresponding to the position. Use something like:
INDEX($P$1:$P$8;A1)
- where A1 represents the cell containing the position value. If you
need to deal with positions beyond eighth, use:
IF(A1<=8;INDEX($P$1:$P$8;A1);0)
In order to carry information over from other sheets, the A1 (or
whatever) cell reference, and perhaps also the array range, will need
to be preceded by the sheet reference, e.g. as Results.A1 if your
results sheet is named "Results".
You can simplify this a great deal if you give a name to your array
of points values. Select the eight cells with the points values and
then use Insert | Names > | Define... to ascribe a name - let's say
"Points" - to the array. Then the above formulae can be simplified to:
INDEX(Points;A1)
and:
IF(A1<=8;INDEX(Points;A1);0)
Apart from being easier to read, this method has the advantages that
you don't have to worry about all those $ signs, necessary to stop
your formula being modified in unwanted ways, or about adding the
sheet name if your array happens to be on a different sheet from
where its values are required. (The required $ signs and sheet name
are buried in the definition of the "Points" name, in fact.)
2. Using a formula:
Try this:
9-A1+INT((9-A1)/7)*MOD(9-A1;6)
or, if you need to cope with positions beyond eighth:
IF(A1<=8;9-A1+INT((9-A1)/7)*MOD(9-A1;6);0)
If you either puzzle out or test these formulae, you will find that
they map your positions into the corresponding points value. So they
work without the array of points values, which you then don't need at all.
All these formulae need to be preceded by an equals sign if they are
not used as part of a larger expression, of course.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
