ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   RANDBETWEEN problems (https://www.excelbanter.com/excel-worksheet-functions/232167-randbetween-problems.html)

plb
RANDBETWEEN problems
 
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)),RANDB ETWEEN(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
RANDBETWEEN problems
 
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" wrote in message
...
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)),RANDB ETWEEN(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
RANDBETWEEN problems
 
Thanks makes sense.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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)),RANDB ETWEEN(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
RANDBETWEEN problems
 
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.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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)),RANDB ETWEEN(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
RANDBETWEEN problems
 
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 !
---------------------------------------------------------------------------
"plb" wrote in message
...
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)),RANDB ETWEEN(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
RANDBETWEEN problems
 
That solved the multiple "0" output and the predictable "00000000" output
that repeats itself using the other method.

Thanks.

"Ragdyer" wrote in message
...
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 !
---------------------------------------------------------------------------
"plb" wrote in message
...
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)),RANDB ETWEEN(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
RANDBETWEEN problems
 
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)),RANDB ETWEEN(1,4))

Fixing that to work with a rectangular region would give

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

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

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

HTH,
Bernie
MS Excel MVP


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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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)),RANDB ETWEEN(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
RANDBETWEEN problems
 
You're welcome, and appreciate the feed-back.
--

Regards,

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

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

Thanks.

"Ragdyer" wrote in message
...
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 !
---------------------------------------------------------------------------
"plb" wrote in message
...
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)),RANDB ETWEEN(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


All times are GMT +1. The time now is 04:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com