Harold I just tried your LOOKUP idea in a separate sheet and it works a treat.
It certainly makes what I thought was a difficult equation into a simple task. Thanks -- Regards Nathaniel http://bytesfromthebackseat.wordpress.com/ On 8/9/07, Brian Barker <[EMAIL PROTECTED]> wrote: > > At 15:27 08/08/2007 +0800, Nathaniel Seventy-Three 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, you don't need to put values in the other cells: if interpreted > as numbers, empty cells will be read as zero anyway. > > But this requires a different exception from the one I'd allowed for, > so you need to modify my three suggested formulae to: > IF(A1>0;INDEX($P$1:$P$8;A1);0) > IF(A1>0;INDEX(Points;A1);0) > IF(A1>0;9-A1+INT((9-A1)/7)*MOD(9-A1;6);0) > > I trust this helps. > > Brian Barker > > >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] > >
