Random Number Help

[rede96]

Hi,

How can I make sure that I can generate random numbers that don't repeat using the rnd function in VBA please?

Thanks

[sweep]

http://www.ozgrid.com/VBA/RandomNumbers.htm

[shg]

See VBE Help for Randomize.

[rede96]

http://www.ozgrid.com/VBA/RandomNumbers.htm

Seen that thanks. I used some of the code.

See VBE Help for Randomize.

Thanks for the tip. I did already look this up but it doesn't solve my problem.

In short, I wrote a small bit of code that generates 6 lottery numbers between 1 and 49 and then it checks for a match against 6 lotto numbers I set manually. If no match then it generates another 6 numbers etc. etc. until a match is found.

I was using the code to do some statistical analysis.

The problem is I can demonstrate that the numbers that the vba code generate cannot be random or are random within a certain limits.

This is because I can have 6 lotto numbers where a match is found each time, within a million tries or so and 6 lotto numbers where no match is found, even after 100 million tries, using randomize.

Statistically, that shouldn't happen.

Anyway, I just wanted to know if I'd done the code wrong or something.

[shg]

Anyway, I just wanted to know if I'd done the code wrong or something.

Maybe (probably). Post your code.

Excel's random number generator 2003 and later is very good.

[rede96]

Maybe (probably). Post your code.

Excel's random number generator 2003 and later is very good.

Doh! Sorry about that.

Code is below. I am using excel 2007

Please Login or Register  to view this content.

[shg]

You're trying to match all six numbers, each between 1 and 49? You need to try more than 10M times; there's almost a 49% chance that it won't occur. There's a 24% chance that it won't occur in 20M tries; a 3% chance that it won't occur in 50M.

[rede96]

You're trying to match all six numbers, each between 1 and 49? You need to try more than 10M times; there's almost a 49% chance that it won't occur. There's a 24% chance that it won't occur in 20M tries; a 3% chance that it won't occur in 50M.

If we take your estimated 49% chance of it not occurring, that would mean I have a 51% chance of it happening. So if I ran the code, say 10 times then I should get a match at about 5 times on average.

The actual probability is 1 in 13,983,816. But that isn't the problem. I couldn't figure out why I can run the code with the numbers 1,15,27,29,35,38 and it will find a match every time after about one million tries or so. But if I change the 15 to a 16 (As shown in the code) then I get no matches.

Statistically, that should not happen.

[shg]

You're trying to match all six numbers, each between 1 and 49? You need to try more than 10M times; there's almost a 49% chance that it won't occur. There's a 24% chance that it won't occur in 20M; a 3% chance that it won't occur in 50M.

EDIT: Here's some alternative code you can try ...

Please Login or Register  to view this content.

[rede96]

Thanks for the code, I have run it and can see the results.

I will have a look at it in detial later as I'm still only beginer when it comes to VB.

However, as far as the problem I am having, I think we are going around in circles.

Just so I have made it clear, I am not trying to match the 6 numbers in the order that they are drawn, i.e. 1, 30, 25... that is why I sort them so I am always matching 1, 25, 30... no mater what order they come out.

You're trying to match all six numbers, each between 1 and 49? You need to try more than 10M times; there's almost a 49% chance that it won't occur.

Excuse me if I have this wrong but....
Although there is only a 51% chance of this happening, it is a 51% chance of it happening every time I run the code, assuming the code is generating truly random numbers.

So it is a bit like flipping a coin. There is always a 50% chance of getting a 'head'. But if I keep flipping I will get a 'head' sooner or later. In fact there is only a 3% chance that I will get 5 tails in a row.

So if I run the code 5 times in a row, it is like flipping the coin, so sooner or later I must get a match as there is only a 2.8% chance that I won’t get a match.

Also, there is still no explanation as to why I can always get a match using one set of numbers, but never get a match, no matter how many times I run the code for the second set. (As shown in my last post.)

This makes me believe that excel is not generating a true set of random numbers.

[shg]

VBA will generate the same sequence of random numbers after being reset (executing an End statement, or pushing the Reset button). The Randomize statement eliminates that.

So I took that out, deliberately reset VBA, ran my code, and got this:

Please Login or Register  to view this content.

[rede96]

Ok, so there must be something wrong with my code then or randomize is not working. (I know, there's a 99.9% chance it is my code!)

Thanks again for the help shg, it is much appreciated.

It's 11pm here in the UK so I'll play around with the alternative code tomorrow and see what I come up with.

Cheers.

[shg]

Post back if you get stuck. Cut me in if you win the lottery.

[rede96]

Hi shg,

I ran your code comparing 2 sets of numbers. Set 1 = {1,2,3,4,5,6} and Set 2 = {1,10,20,30,40,49}

Obviously the laws of probability say that both sets of numbers have the same chance of being found.

I ran the code 10 times for each set of numbers, with nTry = 9,692,842
(i.e. 9.6 million tries.) I chose this number as this was the expected number of tries to find a match.

The result for set 1 was 10 matches out of 10 with an average of 1,873,980 tries per times ran.

The result for set 2 was 0 matches out of 10. I even changed nTry to 40 million, with about a 94% chance of success but still no match.

This demonstrates to me conclusively that excel cannot generate true random numbers, even using the randomize function. Or if it can, there must be a different way to do it.

[shg]

Well, I can verify your results, and have no good answer. I created a histogram array for the ball numbers, and it was very respectably flat.

You might want to look at another random number generator; here's one: http://www.business-spreadsheets.com...s.asp?prod=116 (can't comment on performance or characteristics).

If you do, drop me a PM and let me know how you get on.

[rede96]

will do.

Thanks again for the help.

[AlexFromFrance]

Dear all,

I had a problem concerning the periodicity of the random function in Excel using VBA. I never found the solution of my problem so I created something.

The idea is to add a number all the time different to the result of the random function. This number will come from the Timer "function" which gives us the number of seconds from midnight (with 2 decimals).

Here is the code:

Please Login or Register  to view this content.

This solution fix the problem concerning the periodicity of the rnd function. Hope it helps other people with the same problem!

Any questions, just ask (alexis.thizeau(at)gmail.com)
Alex

[arlu1201]

Alex,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

Also,

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

[AlexFromFrance]

Dear Arlu,

Thank you for your information, I am a new comer...

I did'nt posted any question, it was a solution to this thread.
You have the code to test it and if some people have questions according to this code and what I tried to explain, they just ask me or send an email. I was just trying to help.

Best regards,
Alex