Non repeating random number is set of 6 numbers
Posted by Gareth Hall on December 18, 2001 2:46 AM
Started playing around at work today with a lotto coupon generating program, got a little stuckj when it started repeating numbers in the same game, here is what I have so far. The game draws 6 numbers from a barrel o45 and then 1 number from a different barrel of 45, not important for my program but at least you know.
From the site
http://www.goldencasket.com/powerball/number_frequencies.asp
From A1 to A16 are the numbers 1 to 16, from B1 to B16 are the 16 most frequent numbers sorted in numerical order, in cells D1 to I16 I have the formula =RANDBETWEEN($A$1,$A$16) which picks a number between 1 and 16 and puts it in the cell, in cell K1 I have the formula =VLOOKUP(D1,$A$1:$B$16,2,TRUE)which associates the number from 1 to 16 with the actual game number I want to use. I then paste that across another 5 columns to give me 6 numbers to go on my coupon, trouble is sometimes there is a double in the 6 numbers and I obviously can't fill the coupon in like this, I have tried and If statement but just keep getting circular reference errors. I know I am probably doing this the long way and could probably be made a lot simpler, any help with just not getting repeating numbers would be appreciated.
Posted by Ian Mac on December 18, 2001 4:53 AM
I'll see if I've understood
Try this,
in column A rows 1 - 16 put:
=RAND()
in column B row 1 type (make sure the $'s are correct):
=RANK(A2,$A$1:$A$16)+COUNTIF(A$1:A2,A2)-1
This will give you a unique ranking of the rows in column A.
now you can just select any 6 cells in column B and they will be unique.
Does this help??
Ian Mac
Posted by Gareth Hall on December 18, 2001 3:51 PM
No, the formula is wrong in B16 I end up with =RANK(A17,$A$1:$A$16)+COUNTIF(A$1:A17,A17)-1 after dragging it down, there is no data in cell A17. I can see what you are trying to do and should be able to use that to fix my code, thanks.
Posted by Aladin Akyurek on December 18, 2001 4:12 PM
Gareth --
Just use the part
=RANK(A2,$A$1:$A$16)
Aladin
Posted by Gareth Hall on December 18, 2001 7:16 PM
=RANK(A1,$A$1:$A$16) works much better :) I'll let you know if we have a win.
Posted by Ian Mac on December 19, 2001 1:37 AM
I copied the formula from row 2 of the test I set up, BUT
I'd still use
=RANK(A1,$A$1:$A$16)+COUNTIF(A$1:A1,A1)-1
just in case by some sheer freak of nature (nah, Numbers) you get exactly the same rand(), then both the rankings will be the same.
Ian Mac
