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