was fooin around with excel

sagefr0g · Jan 19, 2009

i made a excel spreadsheet, where the idea was you could shuffle a pack and then graph the true count at various points of depth into the pack.
haven't really the foggiest if i did it right or if the random number generator for excel is bogus as far as shuffling.

so but anyway the way the sheet is set up you can just repeatedly hit ctrl-y and you'll keep generating these TCvsDepth graphs.
so dong this and watching how the graphs mapped out for each shuffle it struck me how so high a percentage of shuffles the true counts were the majority of the time really low at the 1st deck dealt point, rarely going above TC=1 sort of thing.

well anyway i guess like for a six deck pack and if you use full deck integers for determining your TC then at the 1st deck dealt point the highest true count you could get would be 8 if your flooring. so but for a whole bunch of trials so far i've just seen a TC = 4 as the highest.

just it made this question come to mind. does anyone know if their are expected true count frequencies by deck depth for a given pack, for example say a six deck pack?

callipygian · Jan 20, 2009

sagefr0g said:
haven't really the foggiest if i did it right or if the random number generator for excel is bogus as far as shuffling.

I think the Excel RNG is good enough to trust the results; if you don't trust the RNG, you can also find lists of random numbers online.

If you use the Excel RNG, be sure you use the =FLOOR() function rather than the =CEILING() or =ROUND() functions, as Excel defines =RAND() as being [0,1) (i.e. includes 0 but not 1). If you want to generate a random number between 1 and 10 inclusive, =ROUND(RAND()*10,0) will return zero 5% of the time and ten 5% of the time, and one through nine 10% of the time each. =CEILING(RAND()*10,1) will occasionally (~1/1,000,000) return zero, and return ten a little less than 10%, while =FLOOR(RAND()*10,1)+1 will give you exactly 1/10 probability for each number.

sagefr0g said:
you can just repeatedly hit ctrl-y

You can also hit F9 to refresh.

Another helpful shortcut here is F4, which is the "redo" button. For example, if you were to copy the RC's and Paste Special -> Values into a new page, you can save the shoe you generated. The shoe will automatically re-randomize after your paste, and by hitting F4 you can paste another shoe.

As a matter of fact, using F4 and the right arrow key, it's pretty easy to fill up a worksheet with randomized counts: depending on your processor speed and manual dexterity, you can generate and save ~40,000 6D shoes in about 5 minutes. Then you can use the =LARGE(), =SMALL(), and =COUNTIF() functions to calculate a probability distribution (note that this really taxes your processor and can take a pretty long time).

sagefr0g said:
does anyone know if their are expected true count frequencies by deck depth for a given pack, for example say a six deck pack?

I have a bunch of these saved on another computer, but I'm pretty sure they're in some book or other too. I'd probably try Professional Blackjack or Blackjack Attack if I had them on hand. Blackjackincolor also has graphs, but I'm not sure whether it has the raw data (which is presumably what you want to compare).

If none of the above (or if nobody can find them before me), I'll post what I have later.

sagefr0g · Jan 20, 2009

callipygian said:
I think the Excel RNG is good enough to trust the results; if you don't trust the RNG, you can also find lists of random numbers online.

If you use the Excel RNG, be sure you use the =FLOOR() function rather than the =CEILING() or =ROUND() functions, as Excel defines =RAND() as being [0,1) (i.e. includes 0 but not 1). If you want to generate a random number between 1 and 10 inclusive, =ROUND(RAND()*10,0) will return zero 5% of the time and ten 5% of the time, and one through nine 10% of the time each. =CEILING(RAND()*10,1) will occasionally (~1/1,000,000) return zero, and return ten a little less than 10%, while =FLOOR(RAND()*10,1)+1 will give you exactly 1/10 probability for each number.

You can also hit F9 to refresh.

Another helpful shortcut here is F4, which is the "redo" button. For example, if you were to copy the RC's and Paste Special -> Values into a new page, you can save the shoe you generated. The shoe will automatically re-randomize after your paste, and by hitting F4 you can paste another shoe.

As a matter of fact, using F4 and the right arrow key, it's pretty easy to fill up a worksheet with randomized counts: depending on your processor speed and manual dexterity, you can generate and save ~40,000 6D shoes in about 5 minutes. Then you can use the =LARGE(), =SMALL(), and =COUNTIF() functions to calculate a probability distribution (note that this really taxes your processor and can take a pretty long time).

I have a bunch of these saved on another computer, but I'm pretty sure they're in some book or other too. I'd probably try Professional Blackjack or Blackjack Attack if I had them on hand. Blackjackincolor also has graphs, but I'm not sure whether it has the raw data (which is presumably what you want to compare).

If none of the above (or if nobody can find them before me), I'll post what I have later.

wow thanks, just the kind of stuff i was wondering about.
and yes i've seen the stuff in Wong's Professional Blackjack. mostly for like overall packs at some penetration, such as below.
but if you have some stuff that you generated for various depth's for six deck or multiple deck i'd be interested in seeing it.

ccibball50 · Jan 20, 2009

I am no expert, but I have a minor in programming. One thing we learned was that normally a random generator is more than likely not 100 percent random. However, it is so close that it would be relatively unoticeable. This was actually a problem when the first online poker sites were started. If you had the privilage of knowing how the programming worked, you could actaully have a small advantage. Not enough to matter though.

Of course this was years ago, things may have changed since then. It always made me think about slot machines, although I am sure that they are true.

Kasi · Jan 20, 2009

sagefr0g said:
i made a excel spreadsheet, where the idea was you could shuffle a pack and then graph the true count at various points of depth into the pack.
haven't really the foggiest if i did it right or if the random number generator for excel is bogus as far as shuffling.

so but anyway the way the sheet is set up you can just repeatedly hit ctrl-y and you'll keep generating these TCvsDepth graphs.
so dong this and watching how the graphs mapped out for each shuffle it struck me how so high a percentage of shuffles the true counts were the majority of the time really low at the 1st deck dealt point, rarely going above TC=1 sort of thing.

well anyway i guess like for a six deck pack and if you use full deck integers for determining your TC then at the 1st deck dealt point the highest true count you could get would be 8 if your flooring. so but for a whole bunch of trials so far i've just seen a TC = 4 as the highest.

just it made this question come to mind. does anyone know if their are expected true count frequencies by deck depth for a given pack, for example say a six deck pack?

You're getting dangerous :grin:

All I know is I used to the @rand function in Lotus to predict my 6 numbers for the lottery. After a while I noticed, the first "calc" always produced the same results lol. So it began with the same seed every time I re-booted lol.

I asked Lotus for my money back but I'm still waiting lol.

Later I used the time of the day for the initial seed. Got different numbers every time but never actually did win the lottery lmao.

Haven't looked at your sheet but there are ways to get to what you want to kinow. I think lmao.

Even those tables at QFIT can, for the same sim, can be combined to to really confuse things.

If something is supposed to happen 1 in 10 times, you can figure out how likely it is it only happened 75 times in 1000 times instead of 100 times. Whatever the sim says as to freq of TC's can be regarded as equally likely - it's like a 41 sided-coin if the the TC's go from -20 to +20.

Getting that crap by deck-depth can be alot of work.

I don't know, if 6D, and the first 52 cards dealt are 24 2's, 24, 3.s and 4 4's, I guess your tC=+52 RC/5=TC 10?

Hey, you're a weekend warrior - the percentage of times you made X bet at each TC of whatever seemed to be in the ball park lol.

QFIT · Jan 20, 2009

The RNG in Excel is famously awful.

Yes indeed TCs are relatively low in the first deck. For the range of TCs (and RCs for unbalanced) see http://www.blackjackincolor.com/penetration1.htm

iCountNTrack · Jan 20, 2009

QFIT said:
The RNG in Excel is famously awful.

Yes indeed TCs are relatively low in the first deck. For the range of TCs (and RCs for unbalanced) see http://www.blackjackincolor.com/penetration1.htm

Yeah Excel is horrible, with all the money they have, Microsoft couldn't get their statistics right

. I use Wavemetrics's Igor Pro which is the DEFINITIVE numerical and statistical analysis tool.

sagefr0g · Jan 20, 2009

QFIT said:
The RNG in Excel is famously awful.

Yes indeed TCs are relatively low in the first deck. For the range of TCs (and RCs for unbalanced) see http://www.blackjackincolor.com/penetration1.htm

when i made my shuffler and true count vs depth graph generator i envisioned that if i ran the shuffler a whole lot of times and real fast and then took a time lapse snap shot of that, then a sort of blurry image would appear of which would be a representation of what goes on in general for TCvsdepth.
so i guess the image in your link is essentially the same thing?
interesting it maps out like a bell curve on it's side sort of thing.

i'm very curious to know what are the attributes of a top rate RNG as opposed to a awful RNG?

callipygian · Jan 20, 2009

Awful, schmawful. It's good enough for what it's asked to do.

sagefr0g · Jan 20, 2009

callipygian said:
Awful, schmawful. It's good enough for what it's asked to do.

wassat? random pixels in a square?

callipygian · Jan 20, 2009

sagefr0g said:
wassat? random pixels in a square?

Pretty much. It's 32,000 random numbers generated from Excel. It may not be the best RNG ever, but it's good enough for shuffling cards.

You don't need the world's best RNG to do what you're doing.

sagefr0g · Jan 20, 2009

callipygian said:
.....

If you use the Excel RNG, be sure you use the =FLOOR() function rather than the =CEILING() or =ROUND() functions, as Excel defines =RAND() as being [0,1) (i.e. includes 0 but not 1). If you want to generate a random number between 1 and 10 inclusive, =ROUND(RAND()*10,0) will return zero 5% of the time and ten 5% of the time, and one through nine 10% of the time each. =CEILING(RAND()*10,1) will occasionally (~1/1,000,000) return zero, and return ten a little less than 10%, while =FLOOR(RAND()*10,1)+1 will give you exactly 1/10 probability for each number.
....

i guess i'm doing it a convoluted way.
i just put 312 cards in a row of cells, the rand() function above each card, the cards tag (count value) below each card.
then it sorts all of that together by the row the 'random' values are in. lol, and that's the shuffle.
then it figures out the the RC and TC at any given point and graphs the TC vs depth.
maybe i should use the floor() function on the true count values to make it more realistic as to how i count, ie. flooring?

are you saying i should use the FLOOR function in conjunction with the RAND function?

QFIT · Jan 20, 2009

sagefr0g said:
i'm very curious to know what are the attributes of a top rate RNG as opposed to a awful RNG?

There are six tests an RNG should pass. Excel's fails all six.

The main criteria are the period (number of numbers before repeats), dispersion (spread of numbers over the range), and degradation (number of numbers before the output loses randomness). The RNG in CVData has a period of 1000000000000000000000000000000 with no degradation. Excel's degrades after about 20,000 to 30,000. There is also a problem with dispersion. Most RNGs when plotted in two dimensions appear to show good dispersion. However, it turns out that with LCGs (the most common type and that used in Excel), if you plot in three dimensions the numbers look evenly dispersed. But when you rotate the chart in three dimensions, you find that all the numbers fall into planes. Marsaglia said "Random numbers fall mainly in the planes." (Mathematicians have a strange sense of humor.) This can cause unpredictable artifacts to appear when the RNG's flaws interact with the study you are performing.

If you are just looking for a general answer requiring a small number of numbers, Excel is OK. But it wasn't really designed for studies.

Sonny · Jan 20, 2009

Here's some more information on randomness tests:

http://en.wikipedia.org/wiki/Diehard_tests

-Sonny-

callipygian · Jan 20, 2009

sagefr0g said:
i guess i'm doing it a convoluted way.
i just put 312 cards in a row of cells, the rand() function above each card, the cards tag (count value) below each card.
then it sorts all of that together by the row the 'random' values are in. lol, and that's the shuffle.
then it figures out the the RC and TC at any given point and graphs the TC vs depth.

I'm not entirely sure what you're doing here. Are you using the lookup functions (HLOOKUP and VLOOKUP) to sort your shoe? Or are you generating random numbers, then fixing those cells as numbers, then sorting your shoe manually?

sagefr0g said:
maybe i should use the floor() function on the true count values to make it more realistic as to how i count, ie. flooring?

You should use the function which is most appropriate for you, whether it's FLOOR, ROUND, CEILING, TRUNC, or INT. All 5 are appropriate in some cases.

In order to generate my shoe, I have a list of the number of each card (24 aces, 24 twos, etc.) I then pull a random number and turn it into an integer between 1 and the total number of cards remaining. The value associated with that card is decreased by 1 on the next line.

I use the FLOOR function when turning the random number into a card - specifically, if aces are in column A, twos in column B, etc., then =FLOOR(RAND()*SUM(A1:J1),1)+1 will return an integer between 1 and the total number of cards. Apologies if you do it another way.

sagefr0g said:
are you saying i should use the FLOOR function in conjunction with the RAND function?

You can nest Excel functions. So you can put =RAND() in cell A1, and then =A1*10 in cell B1, and =FLOOR(B1,1) in cell C1, and =C1+1 in cell D1. D1 will then have a random number between 1 and 10 ... or you could also put =FLOOR(RAND()*10,1)+1 directly into D1 and get the same result.

sagefr0g · Jan 20, 2009

callipygian said:
I'm not entirely sure what you're doing here. Are you using the lookup functions (HLOOKUP and VLOOKUP) to sort your shoe? Or are you generating random numbers, then fixing those cells as numbers, then sorting your shoe manually?

i haven't learned how to use (HLOOKUP and VLOOKUP) yet. but i sure have plans. lol. i keep seeing those functions in other spread sheets and hearing references to them.
but yes, i'm using rand() to generate random numbers in individual cells that are located over my list of individual cards, 312 in all (one card per cell). then i've got card tags in the cells below the 312 card cells. i select all that and then sort by the row of cells containing the random numbers. the first step of selecting the rows and setting up the sort criteria is always manual, then i can there after just hit the ctrl-y key as many times as i want and new graphs will be generated.
like, if you look at that first image i posted, you'll see the top row is just the number of decks according to how deep in, the second row of data is random numbers that rand() generated, then the row below are my card symbols, then the row below that are the cards tags, then the row below that is the rc just calculated from the card tags, and finally the last row is the tc calculated from the rc and the number of decks deep at that point.

You should use the function which is most appropriate for you, whether it's FLOOR, ROUND, CEILING, TRUNC, or INT. All 5 are appropriate in some cases.

ok yes, that's a step i overlooked.

In order to generate my shoe, I have a list of the number of each card (24 aces, 24 twos, etc.) I then pull a random number and turn it into an integer between 1 and the total number of cards remaining. The value associated with that card is decreased by 1 on the next line.

I use the FLOOR function when turning the random number into a card - specifically, if aces are in column A, twos in column B, etc., then =FLOOR(RAND()*SUM(A1:J1),1)+1 will return an integer between 1 and the total number of cards. Apologies if you do it another way.

You can nest Excel functions. So you can put =RAND() in cell A1, and then =A1*10 in cell B1, and =FLOOR(B1,1) in cell C1, and =C1+1 in cell D1. D1 will then have a random number between 1 and 10 ... or you could also put =FLOOR(RAND()*10,1)+1 directly into D1 and get the same result.

ok, a way different way from what i'm doing. so but i'll try and understand, maybe try and set a sheet up that way. thank you.

QFIT · Jan 20, 2009

Sonny said:
Here's some more information on randomness tests:

http://en.wikipedia.org/wiki/Diehard_tests

-Sonny-

Good reference. I didn't realize it was in WP.

sagefr0g · Jan 21, 2009

callipygian said:
...
You should use the function which is most appropriate for you, whether it's FLOOR, ROUND, CEILING, TRUNC, or INT. All 5 are appropriate in some cases.
...

well, i tryed rounddown, it seemed closest to how i probably function when really counting.
geesh, what a differance from just using rc/(decks unseen) and going with the decimal number values as opposed to rounding down.
it's like living in a different dimension.

:whip:

sagefr0g · Jan 22, 2009

QFIT said:
...
Yes indeed TCs are relatively low in the first deck. For the range of TCs (and RCs for unbalanced) see http://www.blackjackincolor.com/penetration1.htm

it looks as if that graph was generated by, i'm guessing cvdata.
could cvdata generate and display, true count frequencies tabulated by deck depth? in other words could it display data showing how frequently one could expect a true count of one to present at the depth of one deck dealt for a six deck game, and then how frequently one could expect a true count of two to present at the depth of one deck dealt, ect. ?

QFIT · Jan 22, 2009

sagefr0g said:
it looks as if that graph was generated by, i'm guessing cvdata.
could cvdata generate and display, true count frequencies tabulated by deck depth? in other words could it display data showing how frequently one could expect a true count of one to present at the depth of one deck dealt for a six deck game, and then how frequently one could expect a true count of two to present at the depth of one deck dealt, ect. ?

Yes, using the Multi-Betting Strategy feature.

was fooin around with excel

Well-Known Member

Attachments

Well-Known Member

Well-Known Member

Attachments

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Attachments

Well-Known Member

Attachments

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Attachments

Well-Known Member

Well-Known Member