# Blackjack card count formula help

#### leftaid

##### New Member
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
with a card entered in A1,
=VLOOKUP(A1,{2,1;7,0;10,-1;"A",-1},2)

#### Domenic

##### MrExcel MVP
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

##### New Member
Thank you so much that was exactly what I needed.

#### Tazguy37

##### MrExcel MVP

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
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

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
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
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
I see my info, you see yours. It's not always accurate, but it's fun, anyway!

Replies
0
Views
47
Replies
7
Views
274
Replies
5
Views
655
Replies
9
Views
181
Replies
1
Views
283

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.

### Which adblocker are you using?

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

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