I have an Excel workbook that has a macro that acts like a lottery simulator.

Basically you enter your 6 lotto numbers between 1 and 49, as per UK lotto, and then the code simulates lottery draws until it finds a match to all 6 numbers or it exceeds the maximum number of tries entered.

I was getting some weird results and ran a test to see if excel was actually generating random numbers correctly.

I had 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.

So I ran the macro 10 times for each set of numbers, with the maximum number of tries set to 9,692,842, as this is what the macro said was the expected number of tries until a match was found.

The result for set 1 was 10 matches out of 10 attempts, with an average of 1,873,980 tries until a match was found.

The result for set 2 was 0 matches out of 10 attempts. I even changed the number of tries to 40 million but still no match.

Intuitively I know that can’t be correct but is there a way to statistically validate that the numbers generated in the macro can’t truly random?