Re: Microsoft Excel
- From: David Stevenson <bridge2@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 1 Aug 2005 13:11:40 +0100
Mike Preston wrote
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).
I have no idea. I am not technical, which is why i am asking for help. All I know is that my desktop and laptop have Micorosoft Excel in all its glory :) and my PDA has Microsoft Pocket Excel which seems to do the same but not always in an identical fashion - but it translates itself.
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.
I am sure that is easy enough.
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(D 2,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!
Well, when I get a little time I could try to put this in and see what happens!
--
David Stevenson Bridge RTFLB Cats Railways /\ /\
Liverpool, England, UK Fax: +44 870 055 7697 @ @
<bridge2@xxxxxxxxxxx> ICQ 20039682 bluejak on OKB =( + )=
Bridgepage: http://blakjak.com/brg_menu.htm ~
.- Prev by Date: Re: Directing problem: players forget to reshuffle !!??
- Next by Date: Re: Microsoft Excel
- Previous by thread: Forcing or not?
- Next by thread: Re: Microsoft Excel
- Index(es):
Relevant Pages
|
