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" wrote in message
...
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.

Hello Ragdyer from Steved

Excellent thankyou.

"Ragdyer" wrote:

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" wrote in message
...
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*RA ND()),0,6,1)))

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

Hello Harlan from Steved

ok put =RAND() in A1:A40
Then Insert, Name, Define, and typed PRNA then add and ok

I then

C5:H5 [array formula]: using Ctrl Shift Enter

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

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

Done all off the above pushed F9 and on the same row I get the same number
twice.

Am I missing something here.

Cheers



"Harlan Grove" wrote:

"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*RA ND()),0,6,1)))

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

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

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

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

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

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.

"RagDyeR" wrote:
.. 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,LottoNumb ers),
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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--