Random number generator in Excel question for GillesD

[Irvin]

Hello GillesD

I saw a formula for excel that you wrote for someone, creating random numbers. Sorry I did not take note of the thread and I tried searching this forum. I have setup this random number with the following criteria;

This is based on a pool of 40 numbers reference A1 to F1 for the 6 generated numbers.

A1 is =randbetween(1,35) B1 is =randbetween(A1+1,36) etc



I would also like to add certain criteria to the above but still remain random. Unfortunately I am no good at macros;

I want to be able to add the following;

Sum range ie: min 90 max 150 - user adjustable.
Odd: min 2 max 4 - user adjustable.
Low: min 2 max 4 - user adjustable.


So when you press "F9" it creates a random number checks the criteria above and if okay displays it otherwise creates another random number.

I'll have a shot at creating a macro tonight with the "user adjustable" accessable from the spread sheet itself rather than going into the macro all the time.

I can think of a lot more criteria...too many to mention.


Thank you in advance,

Irvin.

PS. You never know, for random picks, this might prove better that Quick Pick, Random Pick, Auto Pick, Lucky Dip...in NZ or whatever you like to call it Since it is still random but with the criteria you set.

 

[GillesD]

Generating random numbers

The Excel function RANDBETWEEN(x,y) can generate a random number between x and y and the distribution of such numbers will be fairly uniform, at least quite satisfactory for lotery purposes.

Your approach is flawed as you will have a extremeky high amount of high numbers, not an uniform distribution. To prove this, repeat for formulas for the 6 numbers on at least 100 lines. You will see quite a large number of lines where the sixth number will be 40.

My approach to your problem would be:

1 - In row #1, in columns A to F, enter the same formula: =RANDBETWEEN(1,40) to generate 6 numbers between 1 and 40.

2 - In row #3, again in columns A to F, use the function SMALL(A1:F1,x) where x takes the value 1 to 6 corresponding to the column number; this will sort the 6 numbers from the lowest to the highest.

3 - In cell G3, enter a formula to check if there is no duplicate values in cells A3 to F3; this formula should give you a TRUE value if all numbers are different.

4 - In subsequent columns on the same row, you can enter formulas to check if the 6 numbers meet some specific criteria, like a sum between two values, a specific odd/even ratio, a mininum spread between the lowest and highest numbers, etc. The result of each formula should be TRUE if it meets the criteria you want. You could set as many conditions as you want.

5 - In a another cell, let's say H1, enter a formula that checks all your conditions (in cells G3 to ....) and return TRUE if all cells have a TRUE value.

6 - If you get FALSE, then the 6 numbers do not meet all of your conditions. Press F9 and new numbers will be calculated and your conditions will be verified again. Repeat until you get a TRUE value.

Of course, if you set quite a few conditions, then it is likely you will have to press F9 a few times to get a 6-numbers combimation that meet all of them. Then it might be worthwhile to have a macro that will recalculate the spreadsheet until you get a TRUE value in H1.

 

[Irvin]

GillesD

Thank you for your reply.

Over the last couple of days I put the spreadsheet together.

I've yet to do a thorough check of the numbers it produces but they do look okay.

I have set each of the 6 numbers to fall within +4/-4 of the median for each ball from my history file. so the range of numbers for ball 1(A1) is 1 to 9. and ball 2 (B1) 6 to 14 etc.

This way I wont get extreme variations like 1,2,3,38,39,40.

This mixed with other filters seems okay.

Only problem now is to reduce the size of my spreadsheet.

This will happen as I improve formulas / use longer macro.

Still learning macros......I'm getting there.

Learing real quick.

My random number generator checks for the following;

Sum, Odd, Low, Deci group, history, ball range, lock in a ball number etc

I know you guys don't believe in random number generation and I wont be using it for playing lotto but it lets me try things out.....ie what if I do this what effect will it have.

 

[system13]

/cut

I know you guys don't believe in random number generation and I wont be using it for playing lotto but it lets me try things out.....ie what if I do this what effect will it have.

Hi Irvin,
It doesn`t matter what someone thinks about RN,everybody
plays for his/her money and is entitled to make their own decisions.
Playing RN from a controlled pool of numbers with preset conditions is not such a bad idea IMO.

Anyway,for choosing random lines I use Lotto Hat software
which has several options as stated:

*Random lines
*Random lines from selected numbers
*Random lines from selected filters
*Random lines from selected numbers and filters

Also one can choose how many lines to generate,as expected.
Really nice app.
Note that I`m not affiliated with it in anyway,just love it for what it offers, and that`s far more than only RN options.

Bye

 

[GillesD]

Thoughts on lottery

Actually, Irvin and system13, you are right. A random choice of 6 numbers has the same chance of winning as any other combination, even if most of those here do not believe this.

I am the odd man out here, betting a single combination most of the times. This combination was randomly selected and I have played it for more than a year (and no I have not won big but who has).

For me, lottery data has been an excellent choice for improving my Excel skills and, during courses I sometime give, it gets everybody attention when I have to make a point on some statistics.

I maintain about 15 statistics about the Canadian lottery 6/49 and in most cases, results are not statistically different from the theorical values that can be expected on a long run.

As I say, 6 consecutive numbers will soon come out (remember that soon is quite relative) as the winning numbers. If 5 consecutive numbers can come out (draw #1748 on Oct. 21, 2000 with numbers 38, 43, 44, 45, 46 and 47), then a 6 consecutive numbers combination is not far out.