If I want a random series of numbers between 1 & 1000 ,for example, but I
only want the numbers 1, 9, 26,31,500, 15, 350, 405, 233,1000. whatever. How
do I create that formula?

With your list of numbers that you actually want in cells A1:A10 (and noting
that you have 10 numbers), the formula is:

=INDEX(A1:A10,RANDBETWEEN(1,10))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"texas7186" wrote:

If I want a random series of numbers between 1 & 1000 ,for example, but I
only want the numbers 1, 9, 26,31,500, 15, 350, 405, 233,1000. whatever. How
do I create that formula?

"texas7186" wrote:
If I want a random series of numbers between 1 & 1000 ,for example,
but I only want the numbers 1, 9, 26,31,500, 15, 350, 405, 233,1000.
whatever. How do I create that formula?

Suppose you have 100 numbers between 1 and 1000, and you want to select a
random set of 10 from among them without duplicates.

Put your numbers in A1:A100. In B1:B100, put the formula =RAND(). Then put
the following formula into C1 and copy into C2:C10:

=INDEX($A$1:$A$100,RANK(B1,$B$1:$B$100))

Be careful with the absolute and relative references.

Also, note that the numbers will change every time you edit any worksheet in
the workbook :-(.

To avoid that, you can copy B1:B100 and paste-special-value back to B1:B100,
replacing the RAND formulas with random constants.

But you will have to repeat the procedure (create the RAND formulas and
copy-and-paste-value over them) each time you want a new set of 10 random
numbers. You can easily create a macro to do that.

Alternatively, put the RAND formula is some unused column, e.g. X1:X100.
Whenever you want a new set of random numbers, copy X1:X100 and
paste-special-valule into B1:B100. This slows down recalculations because
the RAND formulas are always recalculated. But you might not notice it in a
small workbook.

Another alternative: replace =RAND() with =myRAND(), and create the
following VBA function:

Function myRAND(Optional arg) As Double
myRAND = Evaluate("RAND()")
End Function

If you call that function as =myRAND($D$1), for example, you can cause a new
set of random values by editing D1.

One final alternative: use the VBA function provided at
http://www.mcgimpsey.com/excel/udfs/randint.html .

You should comment out the "Application Volatile" line to avoid incessant
regeneration. Also, you might consider replacing Rnd() with
Evaluate("RAND()"). There is a small advantage, since the VBA Rnd() and
Excel RAND() algorithms are different.

texas7186 wrote...
If I want a random series of numbers between 1 & 1000 ,for example, but I
only want the numbers 1, 9, 26,31,500, 15, 350, 405, 233,1000. whatever. How
do I create that formula?

Another approach.

=LOOKUP(RAND(),{0;1;2;3;4;5;6;7;8;9}/10,
{1;9;26;31;500;15;350;405;233;1000})

Hello,

If the series should be complete & non-repeating, select 10 adjacent
cells horizontally and array-enter
=INDEX({1,9,26,31,500,15,350,405,233,1000},VBUniqR andInt(10,10))
or vertically and array-enter
=INDEX({1,9,26,31,500,15,350,405,233,1000},TRANSPO SE(VBUniqRandInt
(10,10)))
My UDF UniqRandInt you can find he
http://sulprobil.com/html/uniqrandint.html
Delete the Application.Volatile command if you do not want to get
changed values each time you press F9.

BTW: The worksheet function RAND() does not guarantee non-repeating
random numbers. It is unlikely, but not impossible (=unsafe).

Regards,
Bernd

The worksheet function RAND() does not
guarantee non-repeating random numbers.
It is unlikely, but not impossible (=unsafe).

I wonder if anyone has ever tested RAND for duplicates. I'm sure someone has
done it at some point.

See how many dupes you get in say, 10 million iterations.

RAND = 15 digits (0 to 9)

=PERMUT(15,10)

=10,897,286,400

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello,

If the series should be complete & non-repeating, select 10 adjacent
cells horizontally and array-enter
=INDEX({1,9,26,31,500,15,350,405,233,1000},VBUniqR andInt(10,10))
or vertically and array-enter
=INDEX({1,9,26,31,500,15,350,405,233,1000},TRANSPO SE(VBUniqRandInt
(10,10)))
My UDF UniqRandInt you can find he
http://sulprobil.com/html/uniqrandint.html
Delete the Application.Volatile command if you do not want to get
changed values each time you press F9.

BTW: The worksheet function RAND() does not guarantee non-repeating
random numbers. It is unlikely, but not impossible (=unsafe).

Regards,
Bernd

P.S.

I once tested for RAND = 0.000000000000000 through 10 million iterations.

Result = 0

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
The worksheet function RAND() does not
guarantee non-repeating random numbers.
It is unlikely, but not impossible (=unsafe).

I wonder if anyone has ever tested RAND for duplicates. I'm sure someone
has done it at some point.

See how many dupes you get in say, 10 million iterations.

RAND = 15 digits (0 to 9)

=PERMUT(15,10)

=10,897,286,400

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello,

If the series should be complete & non-repeating, select 10 adjacent
cells horizontally and array-enter
=INDEX({1,9,26,31,500,15,350,405,233,1000},VBUniqR andInt(10,10))
or vertically and array-enter
=INDEX({1,9,26,31,500,15,350,405,233,1000},TRANSPO SE(VBUniqRandInt
(10,10)))
My UDF UniqRandInt you can find he
http://sulprobil.com/html/uniqrandint.html
Delete the Application.Volatile command if you do not want to get
changed values each time you press F9.

BTW: The worksheet function RAND() does not guarantee non-repeating
random numbers. It is unlikely, but not impossible (=unsafe).

Regards,
Bernd

Hello Biff,

...
P.S.

I once tested for RAND = 0.000000000000000 through 10 million iterations.

Result = 0

--

You are a funny guy :-)
In an earlier version of my UDF redw (http://sulprobil.com/html/
redw.html) I had the line (see 5th row from bottom)
While redw <= swi(i)
My friend and colleague (in a hedge fund then) complained twice that
my UDF wasn't working properly.

I took out the "=" because Rnd CAN return zero (and it did).

Good judgement comes from experience, and experience comes from ...?
(bad judgement :-)

BTW: This does not tell us RAND's shortest possible "circulation".
Actually, that would be good to know ...

Regards,
Bernd