Random Numbers

Hello from Steved

ok I am no thinking straight

What do I need to do please to have a number in a row 1 to 40
as an example below

1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once
six rows deep yes the other rows can have the same numbers as above or below

=INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60))

=RAND() copied H1:H60

If you need to randomly select 6 numbers from the range 1-40 with no repeats:

1. Enter the numbers 1-40 into an un-used column
2. Enter =RAND() into the cells of the adjacent column
3. Sort the two columns by the RAND column

This will jumble the numbers 1-40 randomly. Just pick the first six numbers.
--
Gary''s Student


"Steved" wrote:

> Hello from Steved
>
> ok I am no thinking straight
>
> What do I need to do please to have a number in a row 1 to 40
> as an example below
>
> 1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once
> six rows deep yes the other rows can have the same numbers as above or below
>
> =INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60))
>
> =RAND() copied H1:H60

Hello Gary From Steved

Can your formula devoloped to display
6 columns across and 10 rows deep please.

Thankyou.

"Gary''s Student" wrote:

> If you need to randomly select 6 numbers from the range 1-40 with no repeats:
>
> 1. Enter the numbers 1-40 into an un-used column
> 2. Enter =RAND() into the cells of the adjacent column
> 3. Sort the two columns by the RAND column
>
> This will jumble the numbers 1-40 randomly. Just pick the first six numbers.
> --
> Gary''s Student
>
>
> "Steved" wrote:
>
> > Hello from Steved
> >
> > ok I am no thinking straight
> >
> > What do I need to do please to have a number in a row 1 to 40
> > as an example below
> >
> > 1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once
> > six rows deep yes the other rows can have the same numbers as above or below
> >
> > =INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60))
> >
> > =RAND() copied H1:H60

Steved wrote...
....
>Can your formula devoloped to display
>6 columns across and 10 rows deep please.
....
>"Gary''s Student" wrote:
>>If you need to randomly select 6 numbers from the range 1-40 with no repeats:
>>
>>1. Enter the numbers 1-40 into an un-used column
>>2. Enter =RAND() into the cells of the adjacent column
>>3. Sort the two columns by the RAND column
....

You don't need steps 1 or 3. All you need is a range of 60 cells (10*6)
all containing =RAND(). If that range were named PRNA and the top-left
cell of your 10 by 6 result range were C5, enter the following formula
in C5.

C5:
=COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

Select C5 and fill right into D5:H5. Then select C5:H5 and fill down
into C6:H14.

Hello Harlan from Steve

I think i'm missing something here

I followed what you put below naming the range PNRA

ok =RAND() are in cell A1:F10 I highlighted and then I
Insert Name Define

please I am lost where do I put the below


COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

Also do I change your $C$5:$C$5 to $A$1:$A$1

ok also please what is require to reconize the number between 1 to 40.

Thankyou.

"Harlan Grove" wrote:

> Steved wrote...
> ....
> >Can your formula devoloped to display
> >6 columns across and 10 rows deep please.
> ....
> >"Gary''s Student" wrote:
> >>If you need to randomly select 6 numbers from the range 1-40 with no repeats:
> >>
> >>1. Enter the numbers 1-40 into an un-used column
> >>2. Enter =RAND() into the cells of the adjacent column
> >>3. Sort the two columns by the RAND column
> ....
>
> You don't need steps 1 or 3. All you need is a range of 60 cells (10*6)
> all containing =RAND(). If that range were named PRNA and the top-left
> cell of your 10 by 6 result range were C5, enter the following formula
> in C5.
>
> C5:
> =COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))
>
> Select C5 and fill right into D5:H5. Then select C5:H5 and fill down
> into C6:H14.
>
>

You can try this:

Enter
=RAND()
In AA1 and drag across to BN1,
Then down to BN10,
So you have a 40 column by 10 row array of random numbers.

Then, enter this formula anywhere you wish:

=INDEX(ROW($A1:$A40),RANK(AA1,$AA1:$BN1))

Now, copy this formula across 6 columns.
Then, copy down 10 rows.

This should give you what you're looking for.
You'll get a new set of numbers with each hit of <F9>.

--
HTH,

RD

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



"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello Harlan from Steve
>
> I think i'm missing something here
>
> I followed what you put below naming the range PNRA
>
> ok =RAND() are in cell A1:F10 I highlighted and then I
> Insert Name Define
>
> please I am lost where do I put the below
>
>
> COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))
>
> Also do I change your $C$5:$C$5 to $A$1:$A$1
>
> ok also please what is require to reconize the number between 1 to 40.
>
> Thankyou.
>
> "Harlan Grove" wrote:
>
>> Steved wrote...
>> ....
>> >Can your formula devoloped to display
>> >6 columns across and 10 rows deep please.
>> ....
>> >"Gary''s Student" wrote:
>> >>If you need to randomly select 6 numbers from the range 1-40 with no
>> >>repeats:
>> >>
>> >>1. Enter the numbers 1-40 into an un-used column
>> >>2. Enter =RAND() into the cells of the adjacent column
>> >>3. Sort the two columns by the RAND column
>> ....
>>
>> You don't need steps 1 or 3. All you need is a range of 60 cells (10*6)
>> all containing =RAND(). If that range were named PRNA and the top-left
>> cell of your 10 by 6 result range were C5, enter the following formula
>> in C5.
>>
>> C5:
>> =COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))
>>
>> Select C5 and fill right into D5:H5. Then select C5:H5 and fill down
>> into C6:H14.
>>
>>

"Steved" wrote...
....
>I followed what you put below naming the range PNRA
>
>ok =RAND() are in cell A1:F10 I highlighted and then I
>Insert Name Define
>
>please I am lost where do I put the below
>
>
>COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1))

First, you're missing the initial equal sign.

I may have misunderstood your question. Looks like you want 10 samples of 6
numbers each of which is drawn without replacement from 1-40. If so, then
you still don't need anything more than a 40 cell range each cell in which
containing =RAND(), which I'll still call PRNA. I'll further assume that
PRNA is 40 rows in a single column.

Then, in another range WHICH YOU MUST CHOOSE (but for my convenience, I'll
continue to use C5:H14, so in my case, the cells containing the =RAND()
formula don't overlar C5:H14), select C5:H5 and enter the following array
formula.

C5:H5 [array formula]:
=COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RAND()),0,6,1)))

Select C5:H5 and fill down into C6:H14.

Yes.

In my method we are selectimg 6 random numbers in a column. Select all six
numbers and push copy.

Go to the place in the first row and paste/special with the transpose mark
checked.

This will paste the column into your first row. Now we need to fill rows 2-10
and we will do the same thing
1. re-calculate the random numbers (CNTRL-ALT-F9)
2. re-sort the columns to get fresh random digits
3. copy/paste into the next row

--
Gary''s Student


"Steved" wrote:

> Hello Gary From Steved
>
> Can your formula devoloped to display
> 6 columns across and 10 rows deep please.
>
> Thankyou.
>
> "Gary''s Student" wrote:
>
> > If you need to randomly select 6 numbers from the range 1-40 with no repeats:
> >
> > 1. Enter the numbers 1-40 into an un-used column
> > 2. Enter =RAND() into the cells of the adjacent column
> > 3. Sort the two columns by the RAND column
> >
> > This will jumble the numbers 1-40 randomly. Just pick the first six numbers.
> > --
> > Gary''s Student
> >
> >
> > "Steved" wrote:
> >
> > > Hello from Steved
> > >
> > > ok I am no thinking straight
> > >
> > > What do I need to do please to have a number in a row 1 to 40
> > > as an example below
> > >
> > > 1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once
> > > six rows deep yes the other rows can have the same numbers as above or below
> > >
> > > =INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60))
> > >
> > > =RAND() copied H1:H60

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

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

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

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
>

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