RANDBETWEEN problems

[plb]

I am having problems with generating true random choices in Excel2007.
Hopefully this lines up pretty close.
Columns & rows contain:
M N O P
01
02 A a 0 (
03 B b 1 )
04 C c 2 -
05 D d 3 =
06 E e 4 [
07 F f 5 ]
08 G g 6
09 H h 7
10 I i 8
11 J j 9
12 K k
13 L l
14 M m
15 N n
16 O o
17 P p
18 Q q
19 R r
20 S s
21 T t
22 U u
23 V v
24 W w
25 X x
26 Y y
27 Z z

Lets say in each cell Q1...X1 I have the following formula:

=INDEX($M:$P,RANDBETWEEN(1,COUNTA($M2:$P27)),RANDBETWEEN(1,4))

which is supposed to generate a random choice in each of the cells.

And combining the output in Q1&R1&....&X1 it returns a Passphrase = 000000

The problem is that there is a constant choice of 0 in the generated
passphrase
multiple times and a repetition of 000000 every dozen or so hits of the F9
key.

For Example:

Hiting F9

01 = 000000
02 = 000A0O
03 = 00000F
04 = 01S00d
05 = 006000
06 = n00000
07 = 00S0f0
08 = 0000r9
09 = 000002
10 = G00000
11 = z0A000
12 = 000000

Which leads me to believe RANDBETWEEN isn't working correctly as it seems to
choose the 53rd position [ 0 ] alot. I hit the F9 repeatedly and it still
comes up with
this predictible outcome.

Is there another method of truly getting a random choice in each output
cell?

Thanks,

Phil

 

[Bernie Deitrick]

Phil,

Your table is not properly rectangular - you have 16 blanks in column 0, and
20 in column P - and those return 0 when chosen in your INDEX function.

Since you have 68 values (26 +26 + 10 + 6), use a table that is 17 rows by 4
columns - you'll have to wrap the alphabets around the table a bit.

HTH,
Bernie
MS Excel MVP

 

[plb]

Thanks makes sense.

Phil,

Your table is not properly rectangular - you have 16 blanks in column 0,
and 20 in column P - and those return 0 when chosen in your INDEX
function.

Since you have 68 values (26 +26 + 10 + 6), use a table that is 17 rows by
4 columns - you'll have to wrap the alphabets around the table a bit.

HTH,
Bernie
MS Excel MVP

I am having problems with generating true random choices in Excel2007.
Hopefully this lines up pretty close.
Columns & rows contain:
M N O P
01
02 A a 0 (
03 B b 1 )
04 C c 2 -
05 D d 3 =
06 E e 4 [
07 F f 5 ]
08 G g 6
09 H h 7
10 I i 8
11 J j 9
12 K k
13 L l
14 M m
15 N n
16 O o
17 P p
18 Q q
19 R r
20 S s
21 T t
22 U u
23 V v
24 W w
25 X x
26 Y y
27 Z z

Lets say in each cell Q1...X1 I have the following formula:

=INDEX($M:$P,RANDBETWEEN(1,COUNTA($M2:$P27)),RANDBETWEEN(1,4))

which is supposed to generate a random choice in each of the cells.

And combining the output in Q1&R1&....&X1 it returns a Passphrase =
000000

The problem is that there is a constant choice of 0 in the generated
passphrase
multiple times and a repetition of 000000 every dozen or so hits of the
F9 key.

For Example:

Hiting F9

01 = 000000
02 = 000A0O
03 = 00000F
04 = 01S00d
05 = 006000
06 = n00000
07 = 00S0f0
08 = 0000r9
09 = 000002
10 = G00000
11 = z0A000
12 = 000000

Which leads me to believe RANDBETWEEN isn't working correctly as it seems
to
choose the 53rd position [ 0 ] alot. I hit the F9 repeatedly and it
still comes up with
this predictible outcome.

Is there another method of truly getting a random choice in each output
cell?

Thanks,

Phil

 

[plb]

Ok, I just adjusted the table to 4x17 and the product is still the same. A
constant 4 to 6 "0's" outputted in the generated passphrase and a repetition
of 000000 every fewer F9 hits.

Phil,

Your table is not properly rectangular - you have 16 blanks in column 0,
and 20 in column P - and those return 0 when chosen in your INDEX
function.

Since you have 68 values (26 +26 + 10 + 6), use a table that is 17 rows by
4 columns - you'll have to wrap the alphabets around the table a bit.

HTH,
Bernie
MS Excel MVP

I am having problems with generating true random choices in Excel2007.
Hopefully this lines up pretty close.
Columns & rows contain:
M N O P
01
02 A a 0 (
03 B b 1 )
04 C c 2 -
05 D d 3 =
06 E e 4 [
07 F f 5 ]
08 G g 6
09 H h 7
10 I i 8
11 J j 9
12 K k
13 L l
14 M m
15 N n
16 O o
17 P p
18 Q q
19 R r
20 S s
21 T t
22 U u
23 V v
24 W w
25 X x
26 Y y
27 Z z

Lets say in each cell Q1...X1 I have the following formula:

=INDEX($M:$P,RANDBETWEEN(1,COUNTA($M2:$P27)),RANDBETWEEN(1,4))

which is supposed to generate a random choice in each of the cells.

And combining the output in Q1&R1&....&X1 it returns a Passphrase =
000000

The problem is that there is a constant choice of 0 in the generated
passphrase
multiple times and a repetition of 000000 every dozen or so hits of the
F9 key.

For Example:

Hiting F9

01 = 000000
02 = 000A0O
03 = 00000F
04 = 01S00d
05 = 006000
06 = n00000
07 = 00S0f0
08 = 0000r9
09 = 000002
10 = G00000
11 = z0A000
12 = 000000

Which leads me to believe RANDBETWEEN isn't working correctly as it seems
to
choose the 53rd position [ 0 ] alot. I hit the F9 repeatedly and it
still comes up with
this predictible outcome.

Is there another method of truly getting a random choice in each output
cell?

Thanks,

Phil

 

[Ragdyer]

If you're looking for a true random choice (duplicates accepted) in each of
the six cells, try this:

Place all your characters in a single column, say M1 to M68.
Then enter this formula:

=INDEX($M$1:$M$68,INT(RAND()*68)+1)

And copy across as needed.

 

[plb]

That solved the multiple "0" output and the predictable "00000000" output
that repeats itself using the other method.

Thanks.

If you're looking for a true random choice (duplicates accepted) in each
of
the six cells, try this:

Place all your characters in a single column, say M1 to M68.
Then enter this formula:

=INDEX($M$1:$M$68,INT(RAND()*68)+1)

And copy across as needed.
--
HTH,

RD

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

I am having problems with generating true random choices in Excel2007.
Hopefully this lines up pretty close.
Columns & rows contain:
M N O P
01
02 A a 0 (
03 B b 1 )
04 C c 2 -
05 D d 3 =
06 E e 4 [
07 F f 5 ]
08 G g 6
09 H h 7
10 I i 8
11 J j 9
12 K k
13 L l
14 M m
15 N n
16 O o
17 P p
18 Q q
19 R r
20 S s
21 T t
22 U u
23 V v
24 W w
25 X x
26 Y y
27 Z z

Lets say in each cell Q1...X1 I have the following formula:

=INDEX($M:$P,RANDBETWEEN(1,COUNTA($M2:$P27)),RANDBETWEEN(1,4))

which is supposed to generate a random choice in each of the cells.

And combining the output in Q1&R1&....&X1 it returns a Passphrase =
000000

The problem is that there is a constant choice of 0 in the generated
passphrase
multiple times and a repetition of 000000 every dozen or so hits of the
F9
key.

For Example:

Hiting F9

01 = 000000
02 = 000A0O
03 = 00000F
04 = 01S00d
05 = 006000
06 = n00000
07 = 00S0f0
08 = 0000r9
09 = 000002
10 = G00000
11 = z0A000
12 = 000000

Which leads me to believe RANDBETWEEN isn't working correctly as it seems to
choose the 53rd position [ 0 ] alot. I hit the F9 repeatedly and it
still
comes up with
this predictible outcome.

Is there another method of truly getting a random choice in each output
cell?

Thanks,

Phil

 

[Bernie Deitrick]

In keeping with your original problem, your row count was too high - you included M to P, so you
were looking at 68 rows, not just the rows of your table:

=INDEX($M:$P,RANDBETWEEN(1,COUNTA($M2:$P27)),RANDBETWEEN(1,4))

Fixing that to work with a rectangular region would give

=INDEX($M:$P,RANDBETWEEN(1,COUNTA($M2:$M18)),RANDBETWEEN(1,4))

and making sure to ignore the first row of M, this will not return 0s (unless it is the actual
value 0 in your table)

=INDEX($M2:$P18,RANDBETWEEN(1,COUNTA($M2:$M18)),RANDBETWEEN(1,4))

HTH,
Bernie
MS Excel MVP

Ok, I just adjusted the table to 4x17 and the product is still the same. A constant 4 to 6 "0's"
outputted in the generated passphrase and a repetition of 000000 every fewer F9 hits.

Phil,

Your table is not properly rectangular - you have 16 blanks in column 0, and 20 in column P - and
those return 0 when chosen in your INDEX function.

Since you have 68 values (26 +26 + 10 + 6), use a table that is 17 rows by 4 columns - you'll
have to wrap the alphabets around the table a bit.

HTH,
Bernie
MS Excel MVP

I am having problems with generating true random choices in Excel2007.
Hopefully this lines up pretty close.
Columns & rows contain:
M N O P
01
02 A a 0 (
03 B b 1 )
04 C c 2 -
05 D d 3 =
06 E e 4 [
07 F f 5 ]
08 G g 6
09 H h 7
10 I i 8
11 J j 9
12 K k
13 L l
14 M m
15 N n
16 O o
17 P p
18 Q q
19 R r
20 S s
21 T t
22 U u
23 V v
24 W w
25 X x
26 Y y
27 Z z

Lets say in each cell Q1...X1 I have the following formula:

=INDEX($M:$P,RANDBETWEEN(1,COUNTA($M2:$P27)),RANDBETWEEN(1,4))

which is supposed to generate a random choice in each of the cells.

And combining the output in Q1&R1&....&X1 it returns a Passphrase = 000000

The problem is that there is a constant choice of 0 in the generated passphrase
multiple times and a repetition of 000000 every dozen or so hits of the F9 key.

For Example:

Hiting F9

01 = 000000
02 = 000A0O
03 = 00000F
04 = 01S00d
05 = 006000
06 = n00000
07 = 00S0f0
08 = 0000r9
09 = 000002
10 = G00000
11 = z0A000
12 = 000000

Which leads me to believe RANDBETWEEN isn't working correctly as it seems to
choose the 53rd position [ 0 ] alot. I hit the F9 repeatedly and it still comes up with
this predictible outcome.

Is there another method of truly getting a random choice in each output cell?

Thanks,

Phil

 

[RagDyeR]

You're welcome, and appreciate the feed-back.
--

Regards,

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

That solved the multiple "0" output and the predictable "00000000" output
that repeats itself using the other method.

Thanks.

If you're looking for a true random choice (duplicates accepted) in each
of
the six cells, try this:

Place all your characters in a single column, say M1 to M68.
Then enter this formula:

=INDEX($M$1:$M$68,INT(RAND()*68)+1)

And copy across as needed.
--
HTH,

RD

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

I am having problems with generating true random choices in Excel2007.
Hopefully this lines up pretty close.
Columns & rows contain:
M N O P
01
02 A a 0 (
03 B b 1 )
04 C c 2 -
05 D d 3 =
06 E e 4 [
07 F f 5 ]
08 G g 6
09 H h 7
10 I i 8
11 J j 9
12 K k
13 L l
14 M m
15 N n
16 O o
17 P p
18 Q q
19 R r
20 S s
21 T t
22 U u
23 V v
24 W w
25 X x
26 Y y
27 Z z

Lets say in each cell Q1...X1 I have the following formula:

=INDEX($M:$P,RANDBETWEEN(1,COUNTA($M2:$P27)),RANDBETWEEN(1,4))

which is supposed to generate a random choice in each of the cells.

And combining the output in Q1&R1&....&X1 it returns a Passphrase =
000000

The problem is that there is a constant choice of 0 in the generated
passphrase
multiple times and a repetition of 000000 every dozen or so hits of the
F9
key.

For Example:

Hiting F9

01 = 000000
02 = 000A0O
03 = 00000F
04 = 01S00d
05 = 006000
06 = n00000
07 = 00S0f0
08 = 0000r9
09 = 000002
10 = G00000
11 = z0A000
12 = 000000

Which leads me to believe RANDBETWEEN isn't working correctly as it seems to
choose the 53rd position [ 0 ] alot. I hit the F9 repeatedly and it
still
comes up with
this predictible outcome.

Is there another method of truly getting a random choice in each output
cell?

Thanks,

Phil