Blackjack card count formula help

leftaid

New Member
Joined
Jan 21, 2005
Messages
2
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
with a card entered in A1,
=VLOOKUP(A1,{2,1;7,0;10,-1;"A",-1},2)
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,534
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!
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,534
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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237

ADVERTISEMENT

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

Active Member
Joined
Feb 5, 2003
Messages
426
Why are the operators counting cards - are you trying to check if the punters are changing their bets according to the count?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,534
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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
I see my info, you see yours. It's not always accurate, but it's fun, anyway! :LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top