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

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

Hi. The following article mentions the following...

"...Combining random numbers as in the Wichman-Hill procedure guarantees
that more than 10^13 numbers will be generated before the repetition
begins."

http://support.microsoft.com/kb/828795


Remember when Rand produced Negative numbers?

The RAND function returns negative numbers in Excel 2003

http://support.microsoft.com/kb/828795

= = = = = = = = = =
Dana DeLouis

On 12/8/09 4:47 AM, Bernd P wrote:
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

Hello Dana,

...
"...Combining random numbers as in the Wichman-Hill procedure guarantees
that more than 10^13 numbers will be generated before the repetition
begins."
...

Thank you!

Regards,
Bernd

"Dana DeLouis" wrote:
The RAND function returns negative numbers in Excel 2003
http://support.microsoft.com/kb/828795

Better link: http://support.microsoft.com/kb/834520 .

KB 834520 states that the problem was corrected in a hotfix released on Feb
24, 2004. The hotfix is described in
http://support.microsoft.com/kb/833855/ . And KB 833855 seems to state that
the hotfix is included in Excel 2003 SP1.


----- original message -----

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

Hi. The following article mentions the following...

"...Combining random numbers as in the Wichman-Hill procedure guarantees
that more than 10^13 numbers will be generated before the repetition
begins."

http://support.microsoft.com/kb/828795


Remember when Rand produced Negative numbers?

The RAND function returns negative numbers in Excel 2003

http://support.microsoft.com/kb/828795

= = = = = = = = = =
Dana DeLouis

On 12/8/09 4:47 AM, Bernd P wrote:
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

more than 10^13 numbers will be generated before the repetition begins.

Does that mean there are over 10^13 permutations of RAND() ?

From Excel help:

Returns an evenly distributed random number greater than or equal to 0.....

I just tested 100 million iterations (in Excel 2007) trying to get a result
of exactly 0. The closest I got to exactly 0 was 0.000000000207764472293093.

Not even close, but "only" 100 million iterations.

--
Biff
Microsoft Excel MVP


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

Hi. The following article mentions the following...

"...Combining random numbers as in the Wichman-Hill procedure guarantees
that more than 10^13 numbers will be generated before the repetition
begins."

http://support.microsoft.com/kb/828795


Remember when Rand produced Negative numbers?

The RAND function returns negative numbers in Excel 2003

http://support.microsoft.com/kb/828795

= = = = = = = = = =
Dana DeLouis

On 12/8/09 4:47 AM, Bernd P wrote:
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

"T. Valko" wrote:
Does that mean there are over 10^13 permutations of RAND()?
[....]
I just tested 100 million iterations (in Excel 2007)
trying to get a result of exactly 0.

The short answers are....

I believe we can say that the "period" of the RNG is "over 10^13", as long
as no algorithm parameter in KB 828795 (IX, IY or IZ) is zero initially.
That is, the entire sequence does not repeat itself until "over 10^13" RAND
calls.

And....

I do not believe the current Excel 2003 RAND algorithm would result in
exactly zero, barring defects, if http://support.microsoft.com/kb/828795 is
correct.

The long answers follow, in reverse order.


I just tested 100 million iterations (in Excel 2007)
trying to get a result of exactly 0.

I do not believe the current Excel 2003 RAND algorithm would result in
exactly zero, barring defects, if http://support.microsoft.com/kb/828795 is
correct.

It could take as many as about 2.78E+13 iterations to know for sure, based
on the algorithm and parameters published in KB 828795. I don't have the
patience to wait as long as 60 days on my computer ;-).

On second thought, I believe it requires at most about 1.10E+10 iterations
to try all __relevant__ possibilities. I think that would take "only" a
couple hours on my computer. Moreover, I can reduce that number
significantly. So it takes about 45 min for about 4.13E+09 itertions. (See
results below.)

Mathematically, the formula cannot result in exactly zero unless all 3
parameters (IX, IY and IZ) are zero. That can happen only if the seeding
(initialization) algorithm sets all 3 parameters to zero at the outset.
(See the MOD explanation below.)

I doubt that would be permitted because that would require reseeding the RNG
arbitrarily after the first RAND call in order to avoid returning zero ad
nauseum.

On the other hand, it might be possible for the formula to result in exactly
zero due to the artifacts of floating-point arithmetic. IX/30269 + IY/30307
+ IZ/30323 would have to result in exactly 1 or 2 when represented in 64-bit
floating-point.

But after trying all relevant combinations, I do not believe that is the
case. The closest sum is about 2.00000000000004, which, modulo 1, is about
0.0000000000000359712259978551. FYI, the sum whose result, modulo 1, is
closest to 1 is about 0.999999999999964.

Note that all of the above is based on an implementation of the algorithm
described in KB 828795. I did not actually use Excel's RAND in my analysis.

It is possible that Excel's RAND algorithm might make a special effort to
force infinitesimal decimal fractions to exactly zero before computing the
RAND result modulo 1, effectively changing 2.00000000000004 to exactly 2, in
the spirit of the heuristics described under the title "Example When a Value
Reaches Zero" in http://support.microsoft.com/kb/78113 .

I doubt it; but anything is possible with Excel :-(. Still, I don't think
that particular heuristic applies here because Excel has no problem
subtracting 2 from 2.00000000000004 (as computed by the KB formula, the
result of which is infinitesimally different from the constant), resulting
in about 0.0000000000000359712259978551.

Nevertheless, it is also possible that the actual RAND algorithm differs
from KB 828795 in some way, be it the algorithm parameter values or the
algorithm itself. (I am trying to vet KB 828795 now.)

In any case, I do not know if all of the KB details apply to Excel 2007,
notwithstanding the statement in KB 828795 that they do. If you get the
impression that I do not trust KB articles, you're right ;-). I have seen
many mistakes, especially in KB articles that seek to "clarify"
computational behavior.

Also note that Excel 2010 uses a completely different algorithm for RAND
(Mersenne twister).


Does that mean there are over 10^13 permutations of RAND()?

I believe we can say that the "period" of the RNG is "over 10^13", as long
as no algorithm parameter in KB 828795 (IX, IY or IZ) is zero initially.
That is, the entire sequence does not repeat itself until "over 10^13" RAND
calls.

The MOD formula for each parameter results in the maximum sequence,
excluding zero, if the initial parameter value is non-zero.

Given the algorithm parameters in the KB article (30269, 30307 and 30323),
there are about 2.78E+13 possible sums. I believe the period of the RNG
covers all possible sums, excluding those which can be derived only if one
or more parameters are zero (about 2.75E+09). (See below.)

Since the denominator of each term is a prime number, I believe the sums are
unique mathematically.

However, I cannot say that all of the sums modulo 1 result in unique
floating-point values. I suspect they do. But theoretically, it is
possible that the result of different arithmetic expressions might have the
same representation in 64-bit floating-point.

Thus, it is possible (but I doubt it) that the same floating-point value
might appear more than once in a sequence of "over 10^13" results. But that
does not signal a restart of the entire RNG sequence.

On the other hand, if the seeding algorithm permitted one or more parameters
to be zero at the outset, that would significantly reduce the period of the
RNG, since the parameter(s) would remain zero unless the RNG is reseeded
arbitrarily.

For that reason, I suspect the seeding algorithm would not permit even one
of the parameters to be zero at the outset.

One final note....

Although RAND should not return a value exactly equal to 1, and
mathematically the algorithm cannot, it is possible that Excel might
__display__ some RAND results as 1.0...0 to 14 decimal places due to its
display limit of 15 significant digits. Likewise, it is possible that some
infinitesimal RAND results might be __displayed__ as 0.0...0E+00 to 14
decimal places, even though they are not exactly zero.

But I doubt it.

First, it is possible that Excel's RAND algorithm might avoid that by making
a special effort to ignore those results internally, reducing the period of
the RNG by only a few.

Second, based on my empirical results above, I do not believe that RAND
produces results that close to 0 or 1. So I suspect this a non-issue.


----- original message -----

"T. Valko" wrote in message
...
more than 10^13 numbers will be generated before the repetition begins.

Does that mean there are over 10^13 permutations of RAND() ?

From Excel help:

Returns an evenly distributed random number greater than or equal to
0.....

I just tested 100 million iterations (in Excel 2007) trying to get a
result of exactly 0. The closest I got to exactly 0 was
0.000000000207764472293093.

Not even close, but "only" 100 million iterations.

--
Biff
Microsoft Excel MVP


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

Hi. The following article mentions the following...

"...Combining random numbers as in the Wichman-Hill procedure guarantees
that more than 10^13 numbers will be generated before the repetition
begins."

http://support.microsoft.com/kb/828795


Remember when Rand produced Negative numbers?

The RAND function returns negative numbers in Excel 2003

http://support.microsoft.com/kb/828795

= = = = = = = = = =
Dana DeLouis

On 12/8/09 4:47 AM, Bernd P wrote:
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

Good stuff!

--
Biff
Microsoft Excel MVP


"Joe User" <joeu2004 wrote in message
...
"T. Valko" wrote:
Does that mean there are over 10^13 permutations of RAND()?
[....]
I just tested 100 million iterations (in Excel 2007)
trying to get a result of exactly 0.

The short answers are....

I believe we can say that the "period" of the RNG is "over 10^13", as long
as no algorithm parameter in KB 828795 (IX, IY or IZ) is zero initially.
That is, the entire sequence does not repeat itself until "over 10^13"
RAND
calls.

And....

I do not believe the current Excel 2003 RAND algorithm would result in
exactly zero, barring defects, if http://support.microsoft.com/kb/828795
is
correct.

The long answers follow, in reverse order.


I just tested 100 million iterations (in Excel 2007)
trying to get a result of exactly 0.

I do not believe the current Excel 2003 RAND algorithm would result in
exactly zero, barring defects, if http://support.microsoft.com/kb/828795
is
correct.

It could take as many as about 2.78E+13 iterations to know for sure, based
on the algorithm and parameters published in KB 828795. I don't have the
patience to wait as long as 60 days on my computer ;-).

On second thought, I believe it requires at most about 1.10E+10 iterations
to try all __relevant__ possibilities. I think that would take "only" a
couple hours on my computer. Moreover, I can reduce that number
significantly. So it takes about 45 min for about 4.13E+09 itertions.
(See
results below.)

Mathematically, the formula cannot result in exactly zero unless all 3
parameters (IX, IY and IZ) are zero. That can happen only if the seeding
(initialization) algorithm sets all 3 parameters to zero at the outset.
(See the MOD explanation below.)

I doubt that would be permitted because that would require reseeding the
RNG
arbitrarily after the first RAND call in order to avoid returning zero ad
nauseum.

On the other hand, it might be possible for the formula to result in
exactly
zero due to the artifacts of floating-point arithmetic. IX/30269 +
IY/30307
+ IZ/30323 would have to result in exactly 1 or 2 when represented in
64-bit
floating-point.

But after trying all relevant combinations, I do not believe that is the
case. The closest sum is about 2.00000000000004, which, modulo 1, is
about
0.0000000000000359712259978551. FYI, the sum whose result, modulo 1, is
closest to 1 is about 0.999999999999964.

Note that all of the above is based on an implementation of the algorithm
described in KB 828795. I did not actually use Excel's RAND in my
analysis.

It is possible that Excel's RAND algorithm might make a special effort to
force infinitesimal decimal fractions to exactly zero before computing the
RAND result modulo 1, effectively changing 2.00000000000004 to exactly 2,
in
the spirit of the heuristics described under the title "Example When a
Value
Reaches Zero" in http://support.microsoft.com/kb/78113 .

I doubt it; but anything is possible with Excel :-(. Still, I don't think
that particular heuristic applies here because Excel has no problem
subtracting 2 from 2.00000000000004 (as computed by the KB formula, the
result of which is infinitesimally different from the constant), resulting
in about 0.0000000000000359712259978551.

Nevertheless, it is also possible that the actual RAND algorithm differs
from KB 828795 in some way, be it the algorithm parameter values or the
algorithm itself. (I am trying to vet KB 828795 now.)

In any case, I do not know if all of the KB details apply to Excel 2007,
notwithstanding the statement in KB 828795 that they do. If you get the
impression that I do not trust KB articles, you're right ;-). I have seen
many mistakes, especially in KB articles that seek to "clarify"
computational behavior.

Also note that Excel 2010 uses a completely different algorithm for RAND
(Mersenne twister).


Does that mean there are over 10^13 permutations of RAND()?

I believe we can say that the "period" of the RNG is "over 10^13", as long
as no algorithm parameter in KB 828795 (IX, IY or IZ) is zero initially.
That is, the entire sequence does not repeat itself until "over 10^13"
RAND
calls.

The MOD formula for each parameter results in the maximum sequence,
excluding zero, if the initial parameter value is non-zero.

Given the algorithm parameters in the KB article (30269, 30307 and 30323),
there are about 2.78E+13 possible sums. I believe the period of the RNG
covers all possible sums, excluding those which can be derived only if one
or more parameters are zero (about 2.75E+09). (See below.)

Since the denominator of each term is a prime number, I believe the sums
are
unique mathematically.

However, I cannot say that all of the sums modulo 1 result in unique
floating-point values. I suspect they do. But theoretically, it is
possible that the result of different arithmetic expressions might have
the
same representation in 64-bit floating-point.

Thus, it is possible (but I doubt it) that the same floating-point value
might appear more than once in a sequence of "over 10^13" results. But
that
does not signal a restart of the entire RNG sequence.

On the other hand, if the seeding algorithm permitted one or more
parameters
to be zero at the outset, that would significantly reduce the period of
the
RNG, since the parameter(s) would remain zero unless the RNG is reseeded
arbitrarily.

For that reason, I suspect the seeding algorithm would not permit even one
of the parameters to be zero at the outset.

One final note....

Although RAND should not return a value exactly equal to 1, and
mathematically the algorithm cannot, it is possible that Excel might
__display__ some RAND results as 1.0...0 to 14 decimal places due to its
display limit of 15 significant digits. Likewise, it is possible that
some
infinitesimal RAND results might be __displayed__ as 0.0...0E+00 to 14
decimal places, even though they are not exactly zero.

But I doubt it.

First, it is possible that Excel's RAND algorithm might avoid that by
making
a special effort to ignore those results internally, reducing the period
of
the RNG by only a few.

Second, based on my empirical results above, I do not believe that RAND
produces results that close to 0 or 1. So I suspect this a non-issue.


----- original message -----

"T. Valko" wrote in message
...
more than 10^13 numbers will be generated before the repetition begins.

Does that mean there are over 10^13 permutations of RAND() ?

From Excel help:

Returns an evenly distributed random number greater than or equal to
0.....

I just tested 100 million iterations (in Excel 2007) trying to get a
result of exactly 0. The closest I got to exactly 0 was
0.000000000207764472293093.

Not even close, but "only" 100 million iterations.

--
Biff
Microsoft Excel MVP


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

Hi. The following article mentions the following...

"...Combining random numbers as in the Wichman-Hill procedure guarantees
that more than 10^13 numbers will be generated before the repetition
begins."

http://support.microsoft.com/kb/828795


Remember when Rand produced Negative numbers?

The RAND function returns negative numbers in Excel 2003

http://support.microsoft.com/kb/828795

= = = = = = = = = =
Dana DeLouis

On 12/8/09 4:47 AM, Bernd P wrote:
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

So it takes about 45 min for about 4.13E+09 itertions.
(See results below.)

But after trying all relevant combinations,
the closest sum is about 2.00000000000004,

Hi Joe. I was curious on your seed that gave such a close number, just
for educational curiosity. I may be wrong, but here's what I thought.
Although I can use my Excel vba library, I will take the liberty of
using a math program instead of Excel here.

What we are looking for is:
x/30269 + y/30307 + z/30323 == 2

Rearranging the equation:
(918999161 x + 917846887 y + 917362583 z)/27817185604309 == 2

I'll set the denominator 'd to 27817185604309

(918999161 x + 917846887 y + 917362583 z) == 2 d

We note that there are no solutions except for the Prime numbers given,
or twice the Prime numbers given. The Mod function won't return these
Prime numbers, so this is not a solution.
Using a RHS of 2*d-1 'quickly' returns 3 solutions, but some values are
over 34,000. Hence, these are not solutions either.
With 2d+1, we quickly get ONE solution:

FrobeniusSolve[{918999161 , 917846887 , 917362583}, 2 d + 1]

{26478, 26070, 8037}

So, to find the starting value for x, we want to solve 171*x = 26478

Reduce[171 x == 26478, x, Modulus - 30269]

x == 17679

Doing the same for the other two gives our starting seed that returns a
number close to 2.0`

s = {17679, 11781, 16279}

If we do all three calculations in Parallel..

Mod[s*{171, 172, 170}, {30269, 30307, 30323}] / {30269, 30307, 30323}

{26478 / 30269, 26070 / 30307, 8037 / 30323}

Add them up...

55634371208619 / 27817185604309

Numerically:

2.000000000000036

Or in Excel:
2.00000000000004


The seed that gets close to 1' had one solution at 1*d-1 as above:
{12590, 18526, 14044}

Returning:
0.999999999999964

= = = = = = = = =
Hope I didn't mess this up. :)
Dana DeLouis


On 12/11/09 6:57 PM, Joe User wrote:
"T. Valko" wrote:
Does that mean there are over 10^13 permutations of RAND()?
[....]
I just tested 100 million iterations (in Excel 2007)
trying to get a result of exactly 0.

The short answers are....

I believe we can say that the "period" of the RNG is "over 10^13", as long
as no algorithm parameter in KB 828795 (IX, IY or IZ) is zero initially.
That is, the entire sequence does not repeat itself until "over 10^13" RAND
calls.

And....

I do not believe the current Excel 2003 RAND algorithm would result in
exactly zero, barring defects, if http://support.microsoft.com/kb/828795 is
correct.

The long answers follow, in reverse order.


I just tested 100 million iterations (in Excel 2007)
trying to get a result of exactly 0.

I do not believe the current Excel 2003 RAND algorithm would result in
exactly zero, barring defects, if http://support.microsoft.com/kb/828795 is
correct.

It could take as many as about 2.78E+13 iterations to know for sure, based
on the algorithm and parameters published in KB 828795. I don't have the
patience to wait as long as 60 days on my computer ;-).

On second thought, I believe it requires at most about 1.10E+10 iterations
to try all __relevant__ possibilities. I think that would take "only" a
couple hours on my computer. Moreover, I can reduce that number
significantly. So it takes about 45 min for about 4.13E+09 itertions. (See
results below.)

Mathematically, the formula cannot result in exactly zero unless all 3
parameters (IX, IY and IZ) are zero. That can happen only if the seeding
(initialization) algorithm sets all 3 parameters to zero at the outset.
(See the MOD explanation below.)

I doubt that would be permitted because that would require reseeding the
RNG
arbitrarily after the first RAND call in order to avoid returning zero ad
nauseum.

On the other hand, it might be possible for the formula to result in
exactly
zero due to the artifacts of floating-point arithmetic. IX/30269 + IY/30307
+ IZ/30323 would have to result in exactly 1 or 2 when represented in
64-bit
floating-point.

But after trying all relevant combinations, I do not believe that is the
case. The closest sum is about 2.00000000000004, which, modulo 1, is about
0.0000000000000359712259978551. FYI, the sum whose result, modulo 1, is
closest to 1 is about 0.999999999999964.

Note that all of the above is based on an implementation of the algorithm
described in KB 828795. I did not actually use Excel's RAND in my analysis.

It is possible that Excel's RAND algorithm might make a special effort to
force infinitesimal decimal fractions to exactly zero before computing the
RAND result modulo 1, effectively changing 2.00000000000004 to exactly
2, in
the spirit of the heuristics described under the title "Example When a
Value
Reaches Zero" in http://support.microsoft.com/kb/78113 .

I doubt it; but anything is possible with Excel :-(. Still, I don't think
that particular heuristic applies here because Excel has no problem
subtracting 2 from 2.00000000000004 (as computed by the KB formula, the
result of which is infinitesimally different from the constant), resulting
in about 0.0000000000000359712259978551.

Nevertheless, it is also possible that the actual RAND algorithm differs
from KB 828795 in some way, be it the algorithm parameter values or the
algorithm itself. (I am trying to vet KB 828795 now.)

In any case, I do not know if all of the KB details apply to Excel 2007,
notwithstanding the statement in KB 828795 that they do. If you get the
impression that I do not trust KB articles, you're right ;-). I have seen
many mistakes, especially in KB articles that seek to "clarify"
computational behavior.

Also note that Excel 2010 uses a completely different algorithm for RAND
(Mersenne twister).


Does that mean there are over 10^13 permutations of RAND()?

I believe we can say that the "period" of the RNG is "over 10^13", as long
as no algorithm parameter in KB 828795 (IX, IY or IZ) is zero initially.
That is, the entire sequence does not repeat itself until "over 10^13" RAND
calls.

The MOD formula for each parameter results in the maximum sequence,
excluding zero, if the initial parameter value is non-zero.

Given the algorithm parameters in the KB article (30269, 30307 and 30323),
there are about 2.78E+13 possible sums. I believe the period of the RNG
covers all possible sums, excluding those which can be derived only if one
or more parameters are zero (about 2.75E+09). (See below.)

Since the denominator of each term is a prime number, I believe the sums
are
unique mathematically.

However, I cannot say that all of the sums modulo 1 result in unique
floating-point values. I suspect they do. But theoretically, it is
possible that the result of different arithmetic expressions might have the
same representation in 64-bit floating-point.

Thus, it is possible (but I doubt it) that the same floating-point value
might appear more than once in a sequence of "over 10^13" results. But that
does not signal a restart of the entire RNG sequence.

On the other hand, if the seeding algorithm permitted one or more
parameters
to be zero at the outset, that would significantly reduce the period of the
RNG, since the parameter(s) would remain zero unless the RNG is reseeded
arbitrarily.

For that reason, I suspect the seeding algorithm would not permit even one
of the parameters to be zero at the outset.

One final note....

Although RAND should not return a value exactly equal to 1, and
mathematically the algorithm cannot, it is possible that Excel might
__display__ some RAND results as 1.0...0 to 14 decimal places due to its
display limit of 15 significant digits. Likewise, it is possible that some
infinitesimal RAND results might be __displayed__ as 0.0...0E+00 to 14
decimal places, even though they are not exactly zero.

But I doubt it.

First, it is possible that Excel's RAND algorithm might avoid that by
making
a special effort to ignore those results internally, reducing the period of
the RNG by only a few.

Second, based on my empirical results above, I do not believe that RAND
produces results that close to 0 or 1. So I suspect this a non-issue.


----- original message -----

"T. Valko" wrote in message
...
more than 10^13 numbers will be generated before the repetition begins.

Does that mean there are over 10^13 permutations of RAND() ?

From Excel help:

Returns an evenly distributed random number greater than or equal to
0.....

I just tested 100 million iterations (in Excel 2007) trying to get a
result of exactly 0. The closest I got to exactly 0 was
0.000000000207764472293093.

Not even close, but "only" 100 million iterations.

--
Biff
Microsoft Excel MVP


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

Hi. The following article mentions the following...

"...Combining random numbers as in the Wichman-Hill procedure guarantees
that more than 10^13 numbers will be generated before the repetition
begins."

http://support.microsoft.com/kb/828795


Remember when Rand produced Negative numbers?

The RAND function returns negative numbers in Excel 2003

http://support.microsoft.com/kb/828795

= = = = = = = = = =
Dana DeLouis

On 12/8/09 4:47 AM, Bernd P wrote:
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

"Dana DeLouis" wrote:
I was curious on your seed that gave such a close
number, just for educational curiosity.

I will answer your question below.

But just to clarify: I did not "seed" any algorithm. That is, I did not
start with some initial IX, IY and IZ (the "seed"), then compute some
subsequent IX, IY and IZ by applying the MOD formulas in KB 828795 for one
or more iterations.

Instead, I directly derived the IX, IY and IZ needed to give the desired
result, using an inelegant algorithm.


Although I can use my Excel vba library, I will take the
liberty of using a math program instead of Excel here.
[....]
With 2d+1, we quickly get ONE solution:

I like your approach, especially if it is "quick".

I have questions about some details. I might take it up with you off-line,
if I may. I assume the email address in your posting is valid. If not,
please write to me at joeu2004 "at" hotmail.com from an email address that I
can respond to.


For the parameters that produce the closest sum = 2, you wrote:
{26478 / 30269, 26070 / 30307, 8037 / 30323}

Right.


For the parameters that produce the closest sum < 1, you wrote:
The seed that gets close to 1' had one solution at
1*d-1 as above:
{12590, 18526, 14044}

..... Which produces {IX,IY,IZ} of {3791,4237,22286} after applying the MOD
formulas.

Right.

Very good!


----- original message -----

"Dana DeLouis" wrote in message
...
So it takes about 45 min for about 4.13E+09 itertions.
(See results below.)

But after trying all relevant combinations,
the closest sum is about 2.00000000000004,

Hi Joe. I was curious on your seed that gave such a close number, just
for educational curiosity. I may be wrong, but here's what I thought.
Although I can use my Excel vba library, I will take the liberty of using
a math program instead of Excel here.

What we are looking for is:
x/30269 + y/30307 + z/30323 == 2

Rearranging the equation:
(918999161 x + 917846887 y + 917362583 z)/27817185604309 == 2

I'll set the denominator 'd to 27817185604309

(918999161 x + 917846887 y + 917362583 z) == 2 d

We note that there are no solutions except for the Prime numbers given, or
twice the Prime numbers given. The Mod function won't return these Prime
numbers, so this is not a solution.
Using a RHS of 2*d-1 'quickly' returns 3 solutions, but some values are
over 34,000. Hence, these are not solutions either.
With 2d+1, we quickly get ONE solution:

FrobeniusSolve[{918999161 , 917846887 , 917362583}, 2 d + 1]

{26478, 26070, 8037}

So, to find the starting value for x, we want to solve 171*x = 26478

Reduce[171 x == 26478, x, Modulus - 30269]

x == 17679

Doing the same for the other two gives our starting seed that returns a
number close to 2.0`

s = {17679, 11781, 16279}

If we do all three calculations in Parallel..

Mod[s*{171, 172, 170}, {30269, 30307, 30323}] / {30269, 30307, 30323}

{26478 / 30269, 26070 / 30307, 8037 / 30323}

Add them up...

55634371208619 / 27817185604309

Numerically:

2.000000000000036

Or in Excel:
2.00000000000004


The seed that gets close to 1' had one solution at 1*d-1 as above:
{12590, 18526, 14044}

Returning:
0.999999999999964

= = = = = = = = =
Hope I didn't mess this up. :)
Dana DeLouis


On 12/11/09 6:57 PM, Joe User wrote:
"T. Valko" wrote:
Does that mean there are over 10^13 permutations of RAND()?
[....]
I just tested 100 million iterations (in Excel 2007)
trying to get a result of exactly 0.

The short answers are....

I believe we can say that the "period" of the RNG is "over 10^13", as
long
as no algorithm parameter in KB 828795 (IX, IY or IZ) is zero initially.
That is, the entire sequence does not repeat itself until "over 10^13"
RAND
calls.

And....

I do not believe the current Excel 2003 RAND algorithm would result in
exactly zero, barring defects, if http://support.microsoft.com/kb/828795
is
correct.

The long answers follow, in reverse order.


I just tested 100 million iterations (in Excel 2007)
trying to get a result of exactly 0.

I do not believe the current Excel 2003 RAND algorithm would result in
exactly zero, barring defects, if http://support.microsoft.com/kb/828795
is
correct.

It could take as many as about 2.78E+13 iterations to know for sure,
based
on the algorithm and parameters published in KB 828795. I don't have the
patience to wait as long as 60 days on my computer ;-).

On second thought, I believe it requires at most about 1.10E+10
iterations
to try all __relevant__ possibilities. I think that would take "only" a
couple hours on my computer. Moreover, I can reduce that number
significantly. So it takes about 45 min for about 4.13E+09 itertions.
(See
results below.)

Mathematically, the formula cannot result in exactly zero unless all 3
parameters (IX, IY and IZ) are zero. That can happen only if the seeding
(initialization) algorithm sets all 3 parameters to zero at the outset.
(See the MOD explanation below.)

I doubt that would be permitted because that would require reseeding the
RNG
arbitrarily after the first RAND call in order to avoid returning zero ad
nauseum.

On the other hand, it might be possible for the formula to result in
exactly
zero due to the artifacts of floating-point arithmetic. IX/30269 +
IY/30307
+ IZ/30323 would have to result in exactly 1 or 2 when represented in
64-bit
floating-point.

But after trying all relevant combinations, I do not believe that is the
case. The closest sum is about 2.00000000000004, which, modulo 1, is
about
0.0000000000000359712259978551. FYI, the sum whose result, modulo 1, is
closest to 1 is about 0.999999999999964.

Note that all of the above is based on an implementation of the algorithm
described in KB 828795. I did not actually use Excel's RAND in my
analysis.

It is possible that Excel's RAND algorithm might make a special effort to
force infinitesimal decimal fractions to exactly zero before computing
the
RAND result modulo 1, effectively changing 2.00000000000004 to exactly
2, in
the spirit of the heuristics described under the title "Example When a
Value
Reaches Zero" in http://support.microsoft.com/kb/78113 .

I doubt it; but anything is possible with Excel :-(. Still, I don't think
that particular heuristic applies here because Excel has no problem
subtracting 2 from 2.00000000000004 (as computed by the KB formula, the
result of which is infinitesimally different from the constant),
resulting
in about 0.0000000000000359712259978551.

Nevertheless, it is also possible that the actual RAND algorithm differs
from KB 828795 in some way, be it the algorithm parameter values or the
algorithm itself. (I am trying to vet KB 828795 now.)

In any case, I do not know if all of the KB details apply to Excel 2007,
notwithstanding the statement in KB 828795 that they do. If you get the
impression that I do not trust KB articles, you're right ;-). I have seen
many mistakes, especially in KB articles that seek to "clarify"
computational behavior.

Also note that Excel 2010 uses a completely different algorithm for RAND
(Mersenne twister).


Does that mean there are over 10^13 permutations of RAND()?

I believe we can say that the "period" of the RNG is "over 10^13", as
long
as no algorithm parameter in KB 828795 (IX, IY or IZ) is zero initially.
That is, the entire sequence does not repeat itself until "over 10^13"
RAND
calls.

The MOD formula for each parameter results in the maximum sequence,
excluding zero, if the initial parameter value is non-zero.

Given the algorithm parameters in the KB article (30269, 30307 and
30323),
there are about 2.78E+13 possible sums. I believe the period of the RNG
covers all possible sums, excluding those which can be derived only if
one
or more parameters are zero (about 2.75E+09). (See below.)

Since the denominator of each term is a prime number, I believe the sums
are
unique mathematically.

However, I cannot say that all of the sums modulo 1 result in unique
floating-point values. I suspect they do. But theoretically, it is
possible that the result of different arithmetic expressions might have
the
same representation in 64-bit floating-point.

Thus, it is possible (but I doubt it) that the same floating-point value
might appear more than once in a sequence of "over 10^13" results. But
that
does not signal a restart of the entire RNG sequence.

On the other hand, if the seeding algorithm permitted one or more
parameters
to be zero at the outset, that would significantly reduce the period of
the
RNG, since the parameter(s) would remain zero unless the RNG is reseeded
arbitrarily.

For that reason, I suspect the seeding algorithm would not permit even
one
of the parameters to be zero at the outset.

One final note....

Although RAND should not return a value exactly equal to 1, and
mathematically the algorithm cannot, it is possible that Excel might
__display__ some RAND results as 1.0...0 to 14 decimal places due to its
display limit of 15 significant digits. Likewise, it is possible that
some
infinitesimal RAND results might be __displayed__ as 0.0...0E+00 to 14
decimal places, even though they are not exactly zero.

But I doubt it.

First, it is possible that Excel's RAND algorithm might avoid that by
making
a special effort to ignore those results internally, reducing the period
of
the RNG by only a few.

Second, based on my empirical results above, I do not believe that RAND
produces results that close to 0 or 1. So I suspect this a non-issue.


----- original message -----

"T. Valko" wrote in message
...
more than 10^13 numbers will be generated before the repetition begins.

Does that mean there are over 10^13 permutations of RAND() ?

From Excel help:

Returns an evenly distributed random number greater than or equal to
0.....

I just tested 100 million iterations (in Excel 2007) trying to get a
result of exactly 0. The closest I got to exactly 0 was
0.000000000207764472293093.

Not even close, but "only" 100 million iterations.

--
Biff
Microsoft Excel MVP


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

Hi. The following article mentions the following...

"...Combining random numbers as in the Wichman-Hill procedure
guarantees
that more than 10^13 numbers will be generated before the repetition
begins."

http://support.microsoft.com/kb/828795


Remember when Rand produced Negative numbers?

The RAND function returns negative numbers in Excel 2003

http://support.microsoft.com/kb/828795

= = = = = = = = = =
Dana DeLouis

On 12/8/09 4:47 AM, Bernd P wrote:
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