Random Numbers

[Steved]

Hellom from Steved

I had False change it to true and now recalculating.

{=UniqRandInt(40, False)} now {=UniqRandInt(40, True)}

Thankyou.

Thanks very much Dave

Yes I carried out what you described and yes it created Random Numbers.

I still have a Issue and that is when pushing F9 it will not recalculate.

And to Bernd if you get to read this yes I get a double on the same line

Line 1 No 7, Line 4 No 35, Line 6 No 12, Line 8 No 40, and line 10 12

As pasted below.

26 40 17 12 7 7
29 22 24 12 13 31
1 31 33 29 2 17
35 32 15 39 35 3
38 15 21 31 3 24
19 12 25 26 11 12
34 33 24 40 37 10
28 40 10 22 5 40
28 1 24 5 5 32
12 2 12 16 13 38

Thanks once again Dave.

 

[Max]

Think you might also be interested in this program (full details inside):
http://www.savefile.com/files/7565212
File: Randomization_Lotto_program.xls

It fits the bill here ..

The core functionality is driven via
the "RandLotto" UDF by Dave Hawley & JE McGimpsey

Just enter the settings for the game in B6 to D6 in the sheet: Draw,
for example in your case:

From: 1 (in B6)
To: 40 (in C6)
Pick#: 6 (in D6)

Pick# is the number of numbers in a set to be picked for the game.

The settings entered will be used
in the formula in B2: =randlotto(B6,C6,D6)

Then just click the Draw button to generate as many random sets of unique
numbers from within the range defined under "From" and "To" as desired.

Generated sets of numbers will be frozen in col G from G2 down.

One click of the Draw button generates one set.
To reset / clear col G, just click the Reset button.

The RandLotto UDF and the subs Draw and Reset
for the 2 buttons described are listed in the "Notes" sheet

The program's easy to use, and fun, too !

 

[Dave Peterson]

I've never used =UniqRandInt() but I have used J.E. McGimpsey's version:
http://www.mcgimpsey.com/excel/udfs/randint.html

You may want to take a look (just for curiosity's sake).

 

[Bernd Plumhoff]

Hi Dave and Steved,

I got the idea for my function from JE's older version. He adapted his
algorithm in June similar to mine. What's left is that he could omit the
check whether nCount =1. Then its just a question of personal taste whether
one would like to call a function RandInt() or UniqRandint(), whether one
would like to define the lower and upper border of the numbers or just the
range and whether the "volatility" of the function should be a parameter.

Steved, I hope you could solve your problem?

Regards,
Bernd

 

[Steved]

Thankyou Max.

 

[Steved]

Thankyou Dave.

I've never used =UniqRandInt() but I have used J.E. McGimpsey's version:
http://www.mcgimpsey.com/excel/udfs/randint.html

You may want to take a look (just for curiosity's sake).

 

[Steved]

Hello Bernd from Steved

I like the i'dea Bernd off your development to VBA
I found this to be quite interesting, I conceed that I'm not a expert in this
field however I will overtime work out a solution using your program,
it is just a matter off I dentifying each row and putting in an argument
to exclude any doubles.

Once again Thankyou.

 

[Max]

... I get a double on the same line
.. I will overtime work out a solution using your program,

If I'm not mistaken, you got duplicates on the same line for some lines as
you didn't enter Bernd's UDF as a multi-cell array, re-his advice in his
original response below:

then select cells A1:F1 (< this range selection is important)
enter: =UniqRandInt(40, false)
as array formula (CTRL+SHIFT+ENTER)
and copy this down to A10:F10.

Probably you might have "wrongly" array-entered the UDF into a *single*
cell, say A1, copied it across to F1, then filled A1:F1 down to F10. And
that's why you got a few lines with duplicates within the same line.

Try it again by selecting the range A1:F1 first, then
paste into the *formula bar*: =UniqRandInt(40, FALSE)
and array-enter with CSE

The same formula (with curly braces inserted by Excel):
{=UniqRandInt(40, FALSE)}
will appear in every cell within A1:F1
but each cell will return a different number

Then just select A1:F1 and fill down to say, F10, and there shouldn't be any
more duplicates within the same line (for any one line)

 

[Myrna Larson]

true randomness accepts the possibility of duplication

Just to be picky <g>, what about the situation where you are selecting WITHOUT
REPLACEMENT items at random from a group of unique items? The selection can be
totally random here, and there will be no duplicates.

 

[Steved]

Hello Max from Steved

Yes I followed your instructions and yes their is no doubles

Question Do I have to do this each time meaning why can I not push F9 to
recalculate. At the moment when I push F9 nothing happens.

Thankyou.

 

[Steved]

Hello from Steved


If I highlight the cells A1:F10 edit delete cells and then undo redo it
recalculates

interesting so what have I not done to allow this to recalculate please.

Thankyou.

Hello Max from Steved

Yes I followed your instructions and yes their is no doubles

Question Do I have to do this each time meaning why can I not push F9 to
recalculate. At the moment when I push F9 nothing happens.

Thankyou.

 

[Max]

.. so what have I not done to allow this to recalculate please.

Nothing <g>. Because Bernd's UDF is not volatile, as he had explained
earlier. If you want it to recalc on pressing F9, try using JE's RandInt()
UDF (see link provided by Dave P earlier)

 

[Steved]

Thankyou Max Understood

I just was'nt thinking

Nothing <g>. Because Bernd's UDF is not volatile, as he had explained
earlier. If you want it to recalc on pressing F9, try using JE's RandInt()
UDF (see link provided by Dave P earlier)

 

[Max]

.. Therefore, my hat is off to Harlan if he can accomplish this scenario
*without* the existence of this 40 column by 10 row array of random

numbers.

Think Harlan did accomplish this a few years back ? <g>
(and in a very concise set-up, too !)
re his response at: http://tinyurl.com/b2oan

Here's an adaptation of Harlan's solution to the
OP's pick 6 out of 40 case

Define* a name: LottoNumbers
referring to: =ROW(INDIRECT("1:40"))
*via Insert > Name > Define

Then in A1 enter the formula:
=INT(1+COUNT(LottoNumbers)*RAND())

In B1, enter the array formula:
=LARGE(IF(COUNTIF($A1:A1,LottoNumbers),0,LottoNumbers),
INT(1+(COUNT(LottoNumbers)-COUNT($A1:A1))*RAND()))

Copy B1 to F1

Select A1:F1, fill down to say, F10

A1:F10 will return 10 sets of 6 numbers (from 1-40)
w/o duplicates in any one set

Press F9 to recalc

 

[Max]

You're welcome, Steved !

Btw, I've just posted a response to RagDyeR in the other branch
It's an adaptation of a past, concise solution by Harlan to suit your
situation. You might want to check it out as well ..

 

[Ragdyer]

Do you realize that you have made it necessary for me to now go out and buy
a hat!<g>

 

[Steved]

Hello Max from Steved

Thanks for effort on my issue.

Cheers.

 

[Max]

Do you realize that you have made it necessary
for me to now go out and buy a hat!<g>

Ah, .. but what better reason can there be ? <g>

 

[Max]

Thanks for effort on my issue ..

Pleasure`, Steved.
You've now got a good number
of options to choose from <g>

 

[Steved]

Thankyou Max

I put in =AND(A1:F1) on each line perfect no doubles.