Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANDBETWEEN | Excel Worksheet Functions | |||
Randbetween | Excel Discussion (Misc queries) | |||
RANDBETWEEN | Excel Worksheet Functions | |||
Randbetween | Excel Discussion (Misc queries) | |||
randbetween gives ?NAME | Excel Discussion (Misc queries) |