A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Random Numbers



 
 
Thread Tools Display Modes
  #21  
Old September 11th, 2005, 07:28 AM
Steved
external usenet poster
 
Posts: n/a
Default

Hellom from Steved

I had False change it to true and now recalculating.

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

Thankyou.

"Steved" wrote:

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.

"Dave Peterson" wrote:

It sounds like you pasted it under the ThisWorkbook module.

Once you get to the VBE (alt-f11 is nice)
hit ctrl-r (to see the project explorer)
find your project
rightclick on it
Insert|Module

Paste it there.

(look under the ThisWorkbook module and delete it from there if I guessed
right.)

Then back to excel and recalculate.

Steved wrote:

Yes

top left corner right clicked on excel icon opened it and pasted it.

"Dave Peterson" wrote:

Did you put it in a general module?

Steved wrote:

Hello Bernd from Steved

I've put your UDF in VBA and Copied =UniqRandInt(40, false)

ok it has #NAME?

What have I not done right please

I presume F9 to execute

Thankyou.

"Bernd Plumhoff" wrote:

Hello,

I suggest to insert a VBA module with my UDF function UniqRandInt() (see
www.sulprobil.com),
then select cells A1:F1 enter
=UniqRandInt(40, false)
as array formula (CTRL+SHIFT+ENTER)
and copy this down to A10:F10.

I admire elegant worksheet function solutions, but if you like to have a
general and robust solution, a thoroughly tested UDF function might be the
answer of your choice (don't trust my function, test it!).

HTH,
Bernd




--

Dave Peterson


--

Dave Peterson

  #22  
Old September 11th, 2005, 11:47 AM
Max
external usenet poster
 
Posts: n/a
Default

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 !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #23  
Old September 11th, 2005, 02:52 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

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 wrote:

Hellom from Steved

I had False change it to true and now recalculating.

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

Thankyou.

"Steved" wrote:

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.

"Dave Peterson" wrote:

It sounds like you pasted it under the ThisWorkbook module.

Once you get to the VBE (alt-f11 is nice)
hit ctrl-r (to see the project explorer)
find your project
rightclick on it
Insert|Module

Paste it there.

(look under the ThisWorkbook module and delete it from there if I guessed
right.)

Then back to excel and recalculate.

Steved wrote:

Yes

top left corner right clicked on excel icon opened it and pasted it.

"Dave Peterson" wrote:

Did you put it in a general module?

Steved wrote:

Hello Bernd from Steved

I've put your UDF in VBA and Copied =UniqRandInt(40, false)

ok it has #NAME?

What have I not done right please

I presume F9 to execute

Thankyou.

"Bernd Plumhoff" wrote:

Hello,

I suggest to insert a VBA module with my UDF function UniqRandInt() (see
www.sulprobil.com),
then select cells A1:F1 enter
=UniqRandInt(40, false)
as array formula (CTRL+SHIFT+ENTER)
and copy this down to A10:F10.

I admire elegant worksheet function solutions, but if you like to have a
general and robust solution, a thoroughly tested UDF function might be the
answer of your choice (don't trust my function, test it!).

HTH,
Bernd




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #24  
Old September 11th, 2005, 06:03 PM
Bernd Plumhoff
external usenet poster
 
Posts: n/a
Default

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


  #25  
Old September 11th, 2005, 08:16 PM
Steved
external usenet poster
 
Posts: n/a
Default

Thankyou Max.

"Max" wrote:

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 !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--



  #26  
Old September 11th, 2005, 08:16 PM
Steved
external usenet poster
 
Posts: n/a
Default

Thankyou Dave.

"Dave Peterson" wrote:

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 wrote:

Hellom from Steved

I had False change it to true and now recalculating.

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

Thankyou.

"Steved" wrote:

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.

"Dave Peterson" wrote:

It sounds like you pasted it under the ThisWorkbook module.

Once you get to the VBE (alt-f11 is nice)
hit ctrl-r (to see the project explorer)
find your project
rightclick on it
Insert|Module

Paste it there.

(look under the ThisWorkbook module and delete it from there if I guessed
right.)

Then back to excel and recalculate.

Steved wrote:

Yes

top left corner right clicked on excel icon opened it and pasted it.

"Dave Peterson" wrote:

Did you put it in a general module?

Steved wrote:

Hello Bernd from Steved

I've put your UDF in VBA and Copied =UniqRandInt(40, false)

ok it has #NAME?

What have I not done right please

I presume F9 to execute

Thankyou.

"Bernd Plumhoff" wrote:

Hello,

I suggest to insert a VBA module with my UDF function UniqRandInt() (see
www.sulprobil.com),
then select cells A1:F1 enter
=UniqRandInt(40, false)
as array formula (CTRL+SHIFT+ENTER)
and copy this down to A10:F10.

I admire elegant worksheet function solutions, but if you like to have a
general and robust solution, a thoroughly tested UDF function might be the
answer of your choice (don't trust my function, test it!).

HTH,
Bernd




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

  #27  
Old September 11th, 2005, 08:21 PM
Steved
external usenet poster
 
Posts: n/a
Default

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.

"Bernd Plumhoff" wrote:

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



  #28  
Old September 12th, 2005, 01:58 AM
Max
external usenet poster
 
Posts: n/a
Default

"Steved" wrote:
... 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:

"Bernd Plumhoff" wrote:
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)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #29  
Old September 12th, 2005, 04:29 AM
Myrna Larson
external usenet poster
 
Posts: n/a
Default

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.


  #30  
Old September 12th, 2005, 05:08 AM
Steved
external usenet poster
 
Posts: n/a
Default

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" wrote:

"Steved" wrote:
... 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:

"Bernd Plumhoff" wrote:
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)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Random numbers john liem Worksheet Functions 11 October 11th, 2005 11:06 PM
GENERATE RANDOM NUMBERS BUT EXCLUDE A NUMBER IN THE SEQUENCE Tracker General Discussion 6 August 5th, 2005 02:49 AM
how are random numbers determined hectorvector Worksheet Functions 2 June 10th, 2005 01:55 PM
If I create a random list in Excel, does it repeat numbers? Kelly Worksheet Functions 2 March 30th, 2005 04:05 AM
Random Number Generator Rich Worksheet Functions 4 August 17th, 2004 07:19 PM


All times are GMT +1. The time now is 10:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.