Re: Microsoft Excel
- From: mbpatpas@xxxxxxxxxxxxxxxxxxx (Mike Preston)
- Date: Thu, 28 Jul 2005 02:27:36 GMT
On Thu, 28 Jul 2005 00:24:01 +0100, David Stevenson
<bridge2@xxxxxxxxxxxxxxxxxx> wrote:
>
> I like to score on my PDA which uses a version of Excel. Actually, I
>think the 'version' does not matter, since if something works on my
>desktop it gets converted to something that works on my PDA.
>
> The score-sheet looks something like this:
>
>Brd Vul Dir Contract By Lead Tricks Score
> 1 w 0 3d d hq 2 +150
> 2 r 0 2h l s2 -2 +100
> 3 g 0 4sr p c6 -3 -1000
> 4 a 0 ap 0
> 5 r 0 3nd r da 0 -550
>
> The vulnerability is based on w=white [nv v nv] r=red [v v nv]
>g=green [nv v v] a=amber [v v v] and refers to North/South's
>vulnerability.
>
> The direction is 0 for me sitting North/South, 1 for East/West.
>
> The contract seems obvious from the examples: note that n=no-trumps.
>
> Declarer is shown as d=david p=david's partner l=david's LHO
>r=david's RHO.
>
> Tricks are 0 for making, 1, 2 etc for overtricks, -1, -2 etc for
>undertricks.
>
> I thought it would be nice for it to work out the score until I tried
>to do it. It is way beyond my competence: can anyone help?
It depends. Can your PDA handle setting aside a 2,880 element
2-dimensional array of cells to hold the scores? Say, cells a1:aq72
of a separate worksheet (if multiple worksheets are allowed), or cells
a100:aq172 of the worksheet you are using to input the results (if
multiple worksheets are not allowed).
If so, it is fairly trivial (although tedious) to set up a formula for
each Score cell which will allow a vlookup to get the specific score
associated with that vulnerability, contract and tricks taken.
First, a small variation, that I hope you can live with. If you treat
ap as a contract of level 0 and enter it as 0ap then the logic works.
Your a1:aq72 cells look like this (the x-axis is tricks taken; 3
columns per potential result; A for undoubled, B for doubled, C for
redoubled - this results in 39 columns of scores; the y-axis is
vulnerability and contract - this results in 72 rows of scores - note
that they are in sort order so the order is
clubs/diamonds/hearts/notrump/spades):
n0a 0 0 ..... 0
n1c -350 -1700 ..... X
n1d -350 ....
n1h ...
n1n
n1s
n2c
n2d
n2h
n2n
n2s
....
....
n7s -650 -3500 ...... Y
v0a
....
....
v7s -1300 ..... ...... ?
where X = the score for nv 1cxx making 13 tricks and Y = the score for
nv 7sxx making 13 tricks and ? = the score for v 7sxx making 13
tricks. I didn't look up the scores above for X and XX contracts.
I've never actually played 1c X down 7.
Assuming you have that lookup array available to you and your columns
above are A, B, C, D, E, F and G, then you want to put into Cell H on
each row the following formula:
=vlookup(x,a100:aq171,y,false)*z
Or if you are using a separate sheet (say, named "s" [for scores]):
=vlookup(x,s!a1:aq72,y,false)*z
where
x = the text representation of the vulnerability and the contract
y = the column associated with the number of tricks and the doubled or
redoubled status
z = a negative 1 if your score is negative, otherwise a 1
Let's build them one by one. Assume your first board is in row 2:
x: IF(IF(B2="w",-1,IF(B2="a",1,IF(ISERR(FIND(E2,"lr")),1,-1)*
IF(C2=1,-1,1)*IF(B2="r",1,-1)))=-1,"n","v")&MID(D2,1,2)
Which reads, in English, if you are white, assume non-vul status, if
you are amber, assume vul status. If you are neither white nor amber,
then see if your opponents are playing it, if so, assume declarer is
vulnerable, if not, assume non-vulnerable. If we are East-West negate
the assumption as to vulnerability. Finally, if N/S is red and E/W
are green ("r") then the calculation is right; otherwise negate it
once more. If the result is -1, declarer is not vul, otherwise
declarer is vul.
y: (MID(D2,1,1)+6+G2)*3+1+IF(MID(D2,3,1)="D",1,0)+
IF(MID(D2,3,1)="R",2,0)
Which reads, in English, undoubled contracts are columns 1, 4, 7, 10,
etc. Doubled contracts are columns 2, 5, 8, 11, etc. Redoubled
contracts are columns 3, 6, 9, 12, etc.
z: IF(G2<0,-1,0)*IF(FIND(E2,"dprl")<3,1,-1)
Which reads, in English, if declarer went down assume score should be
negative, which we leave alone if we find that declarer is either you
or partner, but reverse and make positive if declarer is your rho or
lho.
So, the resulting formula is:
=vlookup(IF(IF(B2="w",-1,IF(B2="a",1,IF(ISERR(FIND(E2,"lr")),1,-1)*
IF(C2=1,-1,1)*IF(B2="r",1,-1)))=-1,"n","v")&MID(D2,1,2),a100:aq72,(MID(D2,1,1)+6+G2)*3+1+IF(MID(D2,3,1)="D",1,0)+
IF(MID(D2,3,1)="R",2,0),false)*IF(G2<0,-1,0)*IF(FIND(E2,"dprl")<3,1,-1)
I think. I don't have time to run an actual test, so this is air
code, really. I might have the direction of the score wrong in z, but
this is all the time I have at the moment.
Note: there is no error checking, so if you decide to put in ap for
the contract as opposed to 0ap, then the result isn't zero, but an
erro.
Let me know if you want me to load this and actually test it!
mike
.
- Follow-Ups:
- Re: Microsoft Excel
- From: Mike Preston
- Re: Microsoft Excel
- References:
- Microsoft Excel
- From: David Stevenson
- Microsoft Excel
- Prev by Date: Re: Directing problem: players forget to reshuffle !!??
- Next by Date: Re: Microsoft Excel
- Previous by thread: Microsoft Excel
- Next by thread: Re: Microsoft Excel
- Index(es):
Relevant Pages
|
