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. -- > 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] > > > > > -- Harold Fuchs London, England Please reply *only* to [email protected]
