And what are we doing with this? Well, if your posistion is place in E1 and you want the points in F1, then enter the following in F1: =CalculatePoints(E1)
Simple as that. 2007/8/9, Johnny Rosenberg <[EMAIL PROTECTED]>: > > Ooops.. sent it before I was finished by mistake... > > You can also write your own cell formula. Just write it as a macro, > something like: > > Function CalculatePoints(Position As Integer) As Integer > Select Case Position > Case 1 > CalculatePoints=10 > Case 2 > CalculatePoints=8 > Case 3 To 8 > CalculatePoints=9-Position > Case else > CalculatePoints=0 > End Select > End Function > > > Johnny Rosenberg > > > 2007/8/9, Johnny Rosenberg <[EMAIL PROTECTED]>: > > > > You can also write your own cell formula. Just write it as a macro, > > something like: > > > > Function CalculatePoints(Position As Integer) As Integer > > Select Case Position > > Case 1 > > > > > > End Function > > > > 2007/8/9, Nathaniel <[EMAIL PROTECTED]>: > > > > > > 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] > > > > > > > > > > > > > > > >
