"Randomising" "number"
 

I have a worksheet, cells, b1, b2 and b3 of which each contains a 10-digit
hexadecimal number.

In another cell, I have the following formula: =mid(b1&b2&b3,3,26)

I have two questions about the above formula.

First, how may I change it so that the three 10-digit numbers will not
always be combined in the order b1, b2, b3, but will be combined in "random"
order?

Secondly, how may I change it so that the 30-digit number thus produced is
reduced to 26 by choosing "randomly" whether to begin the 26-digit number at
the 1st number of the 30 or a subsequent one?

For your first question...

C1, copied down to C3:

=RAND()

Then, try the following...

=MID(INDEX(B1:B3,MATCH(SMALL(C1:C3,1),C1:C3,0))&IN DEX(B1:B3,MATCH(SMALL(C
1:C3,2),C1:C3,0))&INDEX(B1:B3,MATCH(SMALL(C1:C3,3) ,C1:C3,0)),3,26)

For your second question...

=MID(INDEX(B1:B3,MATCH(SMALL(C1:C3,1),C1:C3,0))&IN DEX(B1:B3,MATCH(SMALL(C
1:C3,2),C1:C3,0))&INDEX(B1:B3,MATCH(SMALL(C1:C3,3) ,C1:C3,0)),RANDBETWEEN(
1,3),26)

....which requires that you enable the Analysis ToolPak add-in (Tools
Add-ins check Analysis ToolPak)

Hit the F9 key for a new combination of random numbers (actually, I'm
not sure if it's the F9 key for the Windows version of Excel...I'm using
the Mac version).

Hope this helps!

In article ,
"Leslie" wrote:

I have a worksheet, cells, b1, b2 and b3 of which each contains a 10-digit
hexadecimal number.

In another cell, I have the following formula: =mid(b1&b2&b3,3,26)

I have two questions about the above formula.

First, how may I change it so that the three 10-digit numbers will not
always be combined in the order b1, b2, b3, but will be combined in "random"
order?

Secondly, how may I change it so that the 30-digit number thus produced is
reduced to 26 by choosing "randomly" whether to begin the 26-digit number at
the 1st number of the 30 or a subsequent one?

one way:

=MID(B1&B2&B3&B1&B3&B2&B2&B3&B1&B2&B1&B3&B3&B2&B1& B3&B1&B2,
30*INT(RAND()*6)+INT(RAND()*5)+1,26)

In article ,
"Leslie" wrote:

I have a worksheet, cells, b1, b2 and b3 of which each contains a 10-digit
hexadecimal number.

In another cell, I have the following formula: =mid(b1&b2&b3,3,26)

I have two questions about the above formula.

First, how may I change it so that the three 10-digit numbers will not
always be combined in the order b1, b2, b3, but will be combined in "random"
order?

Secondly, how may I change it so that the 30-digit number thus produced is
reduced to 26 by choosing "randomly" whether to begin the 26-digit number at
the 1st number of the 30 or a subsequent one?

Thank you to both Domenic and JE McGimpsey for your replies to my queries.
I'm in the process of trying your suggestions.

"JE McGimpsey" wrote in message
...
one way:

=MID(B1&B2&B3&B1&B3&B2&B2&B3&B1&B2&B1&B3&B3&B2&B1& B3&B1&B2,
30*INT(RAND()*6)+INT(RAND()*5)+1,26)

In article ,
"Leslie" wrote:

I have a worksheet, cells, b1, b2 and b3 of which each contains a
10-digit
hexadecimal number.

In another cell, I have the following formula: =mid(b1&b2&b3,3,26)

I have two questions about the above formula.

First, how may I change it so that the three 10-digit numbers will not
always be combined in the order b1, b2, b3, but will be combined in
"random"
order?

Secondly, how may I change it so that the 30-digit number thus produced
is
reduced to 26 by choosing "randomly" whether to begin the 26-digit number
at
the 1st number of the 30 or a subsequent one?