WAR

  • Thread starter Thread starter Karl_a_r
  • Start date Start date
K

Karl_a_r

I cant figure out how to simulate the playing conditions
of the simple cardgame: war (don't ask me why, just to see
if i can, which I can't) I can't figure out how to get a
list generated that is 52 data points, using 13 numbers,
and never more than 4... would especially like it if I
could get two variables to show 26 random numbers each,
that also follow the above guidelines. I can get close,
have used every dang data analysis tool so far. Also
seems to be one thing.. like 5 of one number, or ALWAYS
COME OUT SEQUENTIALLY. Can someone shed some lite on this
please?
 
LOL.. thanks.. its driving me bonkers.. I was so far
outside the box a minute ago, that I am still looking for
the dang thing.
 
Hi Karl,

Here's something you might want to try. Hardcode the
values 2 through 14. If you have a version of XL that has
the INSERT>SYMBOL option, you can insert the suit symbols.
Try something like this. With the single digit numbers, 2
through 9, enter a space first then the number then a
space then insert the suit symbol. For numbers 10 through
14, enter the number then a space then insert the symbol.

When you have all 52 'cards' represented put them
somewhere in a single column. Say A1:A52. In B1, enter
=RAND() and double click the fill handle. This will copy
the formula down to B52. Now, where you go from here
depends on how much you want to simulate the actual card
game. To shuffle the deck, select both column A and B.
Then do a sort on column B. Then you could create a macro
that splits the deck for the number of players. Choose one
cell somewhere for each player and enter a formula like
=A1. When the 'cards' have been sorted(shuffled) A1 will
be a random selection. You can even create a formula to
see which player has the winning hand and keep score.
Something like =IF(LEFT(D1,2)>LEFT(E1,2),1,"")

I don't know if this helps, but it would be a good
exercise to see how close you can get it to the actual
card game.

Biff
 
I don't know the game, so could be way off base with this, but:-

With whatever 13 numbers you want, listed in A1:A13, in cell B1:-

=OFFSET($A$1,RANDBETWEEN(0,12),)+(OFFSET($A$1,RANDBETWEEN(0,12),)*RANDBETWEEN(0,1))+(OFFSET($A$1,R
ANDBETWEEN(0,12),)*RANDBETWEEN(0,1))+(OFFSET($A$1,RANDBETWEEN(0,12),)*RANDBETWEEN(0,1))

Then just copy down to B52

The first part will ensure you always get at least 1 number, whilst the *RANDBETWEEN(0,1) on the
other 3 elements means that they may or may not be included, so that it is either 1,2,3,4 numbers
being calculated.

This should give you anywhere from 1 to 4 of the numbers in your list, added up in each of the 52
cells. Same principle could be used for 26 random numbers.
 
I think you would need the analysis toolpak addin installed though. Tools / Addins / Analysis
Toolpak
 
Karl_a_r said:
I cant figure out how to simulate the playing conditions
of the simple cardgame: war (don't ask me why, just to see
if i can, which I can't) I can't figure out how to get a
list generated that is 52 data points, using 13 numbers,
and never more than 4... would especially like it if I
could get two variables to show 26 random numbers each,
that also follow the above guidelines. I can get close,
have used every dang data analysis tool so far. Also
seems to be one thing.. like 5 of one number, or ALWAYS
COME OUT SEQUENTIALLY. Can someone shed some lite on this
please?
Click to expand...

Define the following names.

CardNums referring to
=ROW(INDIRECT("1:52"))

Values referring to
="AKQJT98765432"

Suits referring to
="SHDC"

Cards referring to
=MID(Values,1+MOD(CardNums-1,13),1)&" "
&MID(Suits,1+INT((CardNums-1)/13),1)


Now formulas.

B1:
=INDEX(Cards,INT(1+COUNTA(Cards)*RAND()))

C1 [array formula]:
=INDEX(Cards,LARGE(IF(COUNTIF($B1:B2,Cards)=0,CardNums),
1+INT((COUNTA(Cards)-COUNTA($B1:B2))*RAND())))

B2 [array formula]:
=INDEX(Cards,LARGE(IF(COUNTIF(B1,Cards)=0,CardNums),
1+INT((COUNTA(Cards)-1)*RAND())))

C2 [array formula]:
=INDEX(Cards,LARGE(IF(COUNTIF(C1,Cards)+COUNTIF($B1:B2,Cards)=0,
CardNums),1+INT((COUNTA(Cards)-COUNTA($B1:B2)-1)*RAND())))

Fill C1:C2 into D1:AA2. This gives 2 shuffled hands of 26 cards each.


Now to play the game. More formulas. There's no good alternative to using
ancillary formulas to determine which card in which hand is the winner. I'm
assuming suit means nothing, so there can be ties, when there are ties, the
next 2 cards from each hand are played 'down' and the next card from each
hand is played face up. If that's also a tie, repeat this process. If either
hand lacks enough cards to play ties, that counts as a loss. [These are the
rules I used as a kid.]


A1 [array formula]:
=1+3*(MATCH(TRUE,CODE(T(OFFSET(B1,0,3*(ROW(INDIRECT("1:9"))-1),
1,1)))<>CODE(T(OFFSET(B2,0,3*(ROW(INDIRECT("1:9"))-1),1,1))),
0)-1)

A2:
=1+(FIND(LEFT(INDEX(B1:AZ1,A1),1),Values)>
FIND(LEFT(INDEX(B2:AZ2,A1),1),Values))


Hands after first trick. In real play, the players winning the trick isn't
careful about how s/he adds cards from the trick to his/her hand. All the
cards from the trick must all be placed on the bottom of the pile, but there
are no rules about how they should be ordered. That's too much work for me
at the moment, so I'm requiring that the losing player's cards, in order,
are added to the bottom of the winning player's pile first, then the winning
player's cards, in order.


B4:
=IF(COLUMN()-2<SUMPRODUCT(--($B1:$AZ1<>""))-$A1,
OFFSET(B1,0,$A1,1,1),IF($A2=1,
IF(COLUMN()-2<SUMPRODUCT(--($B1:$AZ1<>"")),INDEX($B2:$AZ2,
COLUMN()-1-SUMPRODUCT(--($B1:$AZ1<>""))+$A1),
IF(COLUMN()-2<SUMPRODUCT(--($B1:$AZ1<>""))+$A1,
INDEX($B1:$AZ1,COLUMN()-1-SUMPRODUCT(--($B1:$AZ1<>""))),
"")),""))

B5:
=IF(COLUMN()-2<SUMPRODUCT(--($B2:$AZ2<>""))-$A1,
OFFSET(B2,0,$A1,1,1),IF($A2=2,
IF(COLUMN()-2<SUMPRODUCT(--($B2:$AZ2<>"")),INDEX($B1:$AZ1,
COLUMN()-1-SUMPRODUCT(--($B2:$AZ2<>""))+$A1),
IF(COLUMN()-2<SUMPRODUCT(--($B2:$AZ2<>""))+$A1,
INDEX($B2:$AZ2,COLUMN()-1-SUMPRODUCT(--($B2:$AZ2<>""))),
"")),""))

Fill B4:B5 into C4:BA5. Copy A1:A2 and paste into A4:A5. For hands after
every subsequent trick, copy A4:BA5 and paste into a range 2 rows below the
second hand after the previous trick, so A7:BA8 for hands after the second
trick.

If you'd prefer not having to enter all this, download my test file (in Zip
archive).

ftp://members.aol.com/hrlngrv/war.zip
 
This doesn't mean that you won't get duplicates though. The formula can return the same
combination of numbers more than once, but as it is entirely possible to have a different
combinations add up to the same number, I'm not sure how you would try and exclude that. Like I
said, I don't know the rules, so not sure if that's OK or not.
 
Back
Top