Blackjack card count formula help

[leftaid]

I am a surveillance supervisor. For the operators you can not count cards in their head, I want them to be able to input the card values as they come out on the table. Is it possible for the spread sheet to keep a running count? Card values are as follows
A,K,Q,J,10 all equal -1 each
7,8,9 equal 0
2,3,4,5,6 equal +1
So in other words can a operator see a face card then input 10 and the formula in the cell would know it equals -1 then keep a running count of the inputed cards in a seperate cell.

 

[IML]

with a card entered in A1,
=VLOOKUP(A1,{2,1;7,0;10,-1;"A",-1},2)

 

[Domenic]

First, set up a table in A1:B5 as follows...

0 0
2 1
7 0
10 -1
A -1

Then try the following formula...

=SUMPRODUCT(LOOKUP(D1:D10,A1:A5,B1:B5))

...where D1:D10 are the cells in which you input your card values. Adjust this range accordingly.

Alternatively, you can dispense with the table and try the following formula instead...

=SUMPRODUCT(LOOKUP(D1:D10,{0,0;2,1;7,0;10,-1;"A",-1}))

Hope this help!

 

[leftaid]

Thank you so much that was exactly what I needed.

 

[Tazguy37]

Can one of you (IML or Domenic...or anyone else, really) please explain why it's not needed to enter lookup values for 3-6, 8, 9, etc.? I pretty much understand what's going on here, but that part had me confused. It's great that it works, but I'd like to be able to come up with formulas like that on my own, y'know? Thanks!

 

[Domenic]

Hi Taz!

First I started with the following table...

A -1
K -1
Q -1
J -1
10 -1
7 0
8 0
9 0
2 1
3 1
4 1
5 1
6 1

As you can see it corresponds with the data given by the OP. Then, as you know, LOOKUP requires that the values in the lookup table be in ascending order. So then you get...

2 1
3 1
4 1
5 1
6 1
7 0
8 0
9 0
10 -1
A -1
J -1
K -1
Q -1

As you also know, if LOOKUP can't find the lookup value, it finds the largest value that's less than the lookup value. So with that in mind we then get...

2 1
7 0
10 -1
A -1

If the lookup value is 8, LOOKUP will return 0, if the lookup is Q it will return -1, and so on.

Note that I added 0 in the lookup table to cope with any blank cells that may exist.

Now for the $64,000 question. How did you get that information (ISP, browser, type of computer) in your signature.

 

[Tazguy37]

I went to www.danasoft.comand got a custom sig. I found a webpage with funny computer error messages and put them in there.

 

[brettdj]

Why are the operators counting cards - are you trying to check if the punters are changing their bets according to the count?

 

[Domenic]

Taz,

I asked because my ISP is Sympatico, I'm using a Mac computer and I'm using Safari as my browser. Is it coincidence that the information matches or are you somehow able to extract that information?

 

[Tazguy37]

I see my info, you see yours. It's not always accurate, but it's fun, anyway!