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? -- 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] > >
