Lottery number generation: create a single line function.

[trillicomm]

You must install "Analysis TookPak-VBA" before doing this; check in your Excel's Tools/Add-Ins menu. Paste this long function (see below) in an Excel's worksheet (any cell) and press F9 (calculate) until you see at least 2 identical numbers. Real life's lottery does not have repeating numbers. I can disable this repetition by using cells relation but I want to use just one (1) cell. Any expert wants to challenge? If not, you can use VBA. But it still requires one (1) line of codes.

=RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)

T. Le
This message was edited by trillicomm on 2002-03-29 13:08

 

[Jack in the UK]

I tell you its possible!

not to eat a bucket of tripe and ells its not NO!

 

[Dave Hawley]

Hey that's a neat little function! I have just tried to better it (without VBA) without success. I thought I was onto something with:

=REPT(RANDBETWEEN(1,50) & " ",5)

But alas, the numbers are all the same

IMO, this neat little function is ideally suited to using as defined name. Open this dialog (Ctrl+F3) type in "Lotto" for the name, then paste in the CONCATENATED function.

Now you can use it in any old cell just by typing:

=Lotto

Well, that's the best I could get!

 

[Ian Mac]

Jack, apparently you have the answer??? (I doubt):

"Can a formula do this.. can i cheat?? OK no i wont udf this but formula... YES it can i have such..... works first time ever time. MY Fininacial Director was going to sack me when i got caught doing this test, then i email her now we are kinda pals! "

but if you do then post it here, my bag is packed for London, I can be there in 2 1/2hrs, my friends in Brixton can put me up.

Chris, this I have to see!!!


_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-03-31 06:10

 

[Jay Petrulis]

I agree with Ian Mac -- share the wealth.

So, is the answer going to be provided for all to see or not?

I see numerous responses from those "on the inside" in the public forum. Don't tease the masses, show us the UDF!

Bye,
Jay

 

[Chris Davison]

On 2002-03-30 17:54, Dave Hawley wrote:
IMO, this neat little function is ideally suited to using as defined name. Open this dialog (Ctrl+F3) type in "Lotto" for the name, then paste in the CONCATENATED function.

Now you can use it in any old cell just by typing:

=Lotto

Dave, doesn't that still retain the possibility of it returning equal ball values :

14, 16, 21, 21, 21 ?

We need different numbers on all 5 returns

 

[Jack in the UK]

Chris

Do you ming me asking.. how far have you tested this..

When i VBA (OK VBA is very different regadless whts peopel say) i fould Rand failed i used find instead....)

i have the same kinda results.

This is difficult.. IMPOSSIBLE.. no i do not agree its getting arround the quirks...

Im sure someone good enough will sove this..

 

[Chris Davison]

Jack - I haven't tested anythign much..... I thought about the problem for about an hour or two and concluded it couldn't be done in non-VBA, so if anyone can, I'll eat those jellied eels.

I just can't see how you maintain the first random number chosen within the rest of the formula - you can't refer back to it without declaring another seperate random number

I've given up on it, it can't be done in a single formula.

 

[Aladin Akyurek]

On 2002-04-01 14:28, Chris Davison wrote:
Jack - I haven't tested anythign much..... I thought about the problem for about an hour or two and concluded it couldn't be done in non-VBA, so if anyone can, I'll eat those jellied eels.

I just can't see how you maintain the first random number chosen within the rest of the formula - you can't refer back to it without declaring another seperate random number

I've given up on it, it can't be done in a single formula. /board/images/smiles/icon_smile.gif

Following Chris's earlier British specs, 6 different numbers from 1 to 49, I'd suggest:

=RANDBETWEEN(1,7)&" "&RANDBETWEEN(8,14)&" "&RANDBETWEEN(15,21)&" "&RANDBETWEEN(22,28)&" "&RANDBETWEEN(29,35)&" "&RANDBETWEEN(36,42)&" "&RANDBETWEEN(43,49)

& no British breakfast, thanks, as specified again by Chris .

Aladin

 

[Chris Davison]

how would balls 1, 2 and 5 ever appear in that scenario Aladin ?

that formula takes 6 different populations of equal size....

the "challenge" was 6 different numbers from a single population

 

[Jack in the UK]

Hi guys

this is the best i ever got to i hopped we could solve this
YES this we do as asks, but sometimes will fail with 2 the same.. 99% on target thou:

tRY IT TEST IT HIT f9 TO CHANGE THE NUMBERS. NO BAD I FEEL...

btw AS MY FORMULS SAYS.. please IF YOU WILL THE MILLIONS HELP A FEW GUYS WHO HAVE NO MONEY OF EXCEL TO PLAY WITH.. please GIVE THEM A CHANGE......

|||||||||||
="JackintheUK" &
CHAR(10) &
RANDBETWEEN(0,48)+1&
" "&
RANDBETWEEN(0,48)+1&
" "&
RANDBETWEEN(0,48)+1&
" "&
RANDBETWEEN(0,48)+1
& " "&
RANDBETWEEN(0,48)+1
&" "&
RANDBETWEEN(0,48)+1
&" " &
CHAR(10) &
"If you win PLEASE help others..."