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
  #11  
Old September 10th, 2005, 05:56 AM
Harlan Grove
external usenet poster
 
Posts: n/a
Default

"Steved" wrote...
....
=COUNTIF(PRNA,"="&TRANSPOSE(OFFSET(PRNA,INT(35*R AND()),0,6,1)))

....

Sorry, I screwed this up. Swap the 6 and 1 arguments, so

=COUNTIF(PRNA,"="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,1,6)))


  #12  
Old September 10th, 2005, 09:12 AM
Steved
external usenet poster
 
Posts: n/a
Default

Hello Harlan from Steved

Harlan done as discribed below but still have the issue offsame number twice
omn the same line

in this case row 8 i've 13 twice and row 10 I have 24 twice.

Is it possible please to have this issue where their are no doubles.
Thankyou for your patience.

"Harlan Grove" wrote:

"Steved" wrote...
....
=COUNTIF(PRNA,"="&TRANSPOSE(OFFSET(PRNA,INT(35*R AND()),0,6,1)))

....

Sorry, I screwed this up. Swap the 6 and 1 arguments, so

=COUNTIF(PRNA,"="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,1,6)))



  #13  
Old September 10th, 2005, 07:36 PM
Bernd Plumhoff
external usenet poster
 
Posts: n/a
Default

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


  #14  
Old September 10th, 2005, 07:49 PM
RagDyeR
external usenet poster
 
Posts: n/a
Default

You're *not* looking for random numbers, since true randomness accepts the
possibility of duplication, since each item has an equal chance of occurring
at each interval.

You're looking for a random *order* of specific items (numbers 1 to 40).

So, you therefore need, first of all, the specific items so that they can be
rearranged randomly.
That's the numbers of the rows, 1 to 40, making the construction of an
actual list unnecessary.

Next, the list is rearranged virtually, by accessing the 40 columns of
random numbers and ranking these numbers, where this ranking is mirrored in
the indexed row numbers.
Even if these *true* random numbers are duplicated, and they are ranked
*equally*, since they designate the row numbers, there *cannot* be a
duplicated return, since there are *no* duplicate row numbers existing.

And since each row must be independent of the other rows, so that
duplication is avoided, you need a separate row of random numbers to be
ranked differently from the others.

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.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------


"Steved" wrote in message
...
Hello Harlan from Steved

Harlan done as discribed below but still have the issue offsame number twice
omn the same line

in this case row 8 i've 13 twice and row 10 I have 24 twice.

Is it possible please to have this issue where their are no doubles.
Thankyou for your patience.

"Harlan Grove" wrote:

"Steved" wrote...
....
=COUNTIF(PRNA,"="&TRANSPOSE(OFFSET(PRNA,INT(35*R AND()),0,6,1)))

....

Sorry, I screwed this up. Swap the 6 and 1 arguments, so

=COUNTIF(PRNA,"="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,1,6)))





  #15  
Old September 10th, 2005, 10:49 PM
Steved
external usenet poster
 
Posts: n/a
Default

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



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

Hello RagDyeR from Steved

I am using your formula and so far I have'nt got any doubles.

I've tried it several times yet to produce a double.

So yes I'm Using it.

Thankyou

"RagDyeR" wrote:

You're *not* looking for random numbers, since true randomness accepts the
possibility of duplication, since each item has an equal chance of occurring
at each interval.

You're looking for a random *order* of specific items (numbers 1 to 40).

So, you therefore need, first of all, the specific items so that they can be
rearranged randomly.
That's the numbers of the rows, 1 to 40, making the construction of an
actual list unnecessary.

Next, the list is rearranged virtually, by accessing the 40 columns of
random numbers and ranking these numbers, where this ranking is mirrored in
the indexed row numbers.
Even if these *true* random numbers are duplicated, and they are ranked
*equally*, since they designate the row numbers, there *cannot* be a
duplicated return, since there are *no* duplicate row numbers existing.

And since each row must be independent of the other rows, so that
duplication is avoided, you need a separate row of random numbers to be
ranked differently from the others.

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.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------


"Steved" wrote in message
...
Hello Harlan from Steved

Harlan done as discribed below but still have the issue offsame number twice
omn the same line

in this case row 8 i've 13 twice and row 10 I have 24 twice.

Is it possible please to have this issue where their are no doubles.
Thankyou for your patience.

"Harlan Grove" wrote:

"Steved" wrote...
....
=COUNTIF(PRNA,"="&TRANSPOSE(OFFSET(PRNA,INT(35*R AND()),0,6,1)))

....

Sorry, I screwed this up. Swap the 6 and 1 arguments, so

=COUNTIF(PRNA,"="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,1,6)))






  #17  
Old September 10th, 2005, 11:37 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

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
  #18  
Old September 11th, 2005, 03:08 AM
Steved
external usenet poster
 
Posts: n/a
Default

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

  #19  
Old September 11th, 2005, 03:50 AM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

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
  #20  
Old September 11th, 2005, 07:21 AM
Steved
external usenet poster
 
Posts: n/a
Default

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

 




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 08:34 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.