Harold Fuchs wrote:
On 08/08/07, Nathaniel <[EMAIL PROTECTED]> wrote:
Thanks for all the info guys I'm having a look at it now. One problem I
can
see is I have the drivers listed 1 per row from 1 to 25 and I just enter
the
finishing position from 1 to 8 in the col for the respective race which
leaves the other 17 cells empty.
Do I need to have zero in these cells or can they be handled as it?
No!!! See my comments about the LOOKUP function and why you need *nine*
scores, the last being zero.
What about inserting a line below each driver's line, containing per
race points (calculated using any of the methods mentioned), and then
doing a simple summation of the row, placing the result on the driver
line? Then blank cells won't matter.
The extra line can be hidden, so it would look the same. Or are there
pitfalls?
Frode Petersen
--
Regards
Nathaniel
http://bytesfromthebackseat.wordpress.com/
On 8/8/07, Brian Barker <[EMAIL PROTECTED]> wrote:
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
