Betchain usa friendly 728x90

Author Topic: Excel's Random Generator Is Weak?  (Read 2455 times)

Reyth

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3318
  • Thanked: 980 times
Excel's Random Generator Is Weak?
« on: August 05, 2015, 04:51:12 PM »
My research, I do with the  RNG of windows excel.
I've heard it said that the excel RNG is weak with a not very big loop/cycle. I would suggest be careful coming to any definitive conclusions with the sole use of the excel RNG.Working with a healthy variety of number generators should not be underestimated.

NOTE:  The solution in this post is mocked and derided by industry professionals.  Better is sqzbox's supa awesome link to NTRAND below.

SUMMARY
Microsoft Excel allows the generation of random numbers. The function RAND() follows the same pattern every time it is used, but alternate methods are available to generate the random numbers.

MORE INFORMATION
The following formula calculates a random number between 0 and 1 that does not follow any pattern:

    =(NOW()*100000 - INT(NOW()*100000))

This formula uses the serial value that Microsoft Excel uses to keep track of time. The values in the 10E-6 position and beyond change so rapidly that they have cycled through a complete set (0 through 9) and more by the time Microsoft Excel finishes a single calculation cycle. Hence, shifting the value of NOW() six digits to the left of the decimal (NOW()*100000) and then subtracting the integer portion of the same number (INT(NOW()*100000)) leaves only the portion to the right of the decimal point, which changes rapidly enough so that execution of a Calculate Now command cannot be guessed. This value also happens to be in the same format used by RAND() (that is, a decimal number from 0 to 1).  If necessary, 100000 can be changed to a larger or different value.

An alternative method of changing the RAND function is to add RANDOMIZE=1 [Microsoft Excel] to

WIN.INI if using Excel version 2.x
EXCEL.INI if using Excel version 3.0
EXCEL4.INI if using Excel version 4.0

This change allows Microsoft Excel to use the system clock to change the seed value.

============
============

I thought this was so interesting.

So Dobble, how do you generate your random numbers?
« Last Edit: August 05, 2015, 11:27:57 PM by Reyth »


 

sqzbox

  • New
  • **
  • Posts: 60
  • Thanked: 5 times
Re: Excel's Random Generator Is Weak?
« Reply #1 on: August 05, 2015, 10:56:50 PM »
There are a number of reasons why RAND() in Excel is not a particularly good one to use. However it is adequate for most simple situations. But if you are planning on using Excel for some serious Monte Carlo simulations then I'd suggest using something else. One alternative that I have heard good things about is found here w3.ntrand dot com.

And for a knowledgeable discussion I've attached a short article.
« Last Edit: August 05, 2015, 11:13:16 PM by Reyth »
 

Reyth

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3318
  • Thanked: 980 times
Re: Excel's Random Generator Is Weak?
« Reply #2 on: August 05, 2015, 11:14:18 PM »
Wow thanks this all looks awesome! : D

I mean holy crud that Excel add-on is AMAZING!

LOL that article you just trashed the OP. X D

Thanks that is so helpful!

I am surprised she didn't trash PRNG's as a whole.
« Last Edit: August 05, 2015, 11:23:29 PM by Reyth »
 

dobbelsteen

  • Hero Member
  • ******
  • Posts: 1256
  • Thanked: 307 times
Re: Excel's Random Generator Is Weak?
« Reply #3 on: August 07, 2015, 10:00:52 AM »
The excel RNG is true enough for the reseurch of a random row.  Roulette is not a topic to draw scientisfically conclusions

 

Mike

  • Veteran Member
  • ****
  • Posts: 557
  • Thanked: 23 times
Re: Excel's Random Generator Is Weak?
« Reply #4 on: August 12, 2015, 06:04:13 PM »
It's easy to get high quality random numbers from random.org. Just put  0 to 36 in the integer value fields.

https://www.random.org/integers/
 

Trilobite

  • Veteran Member
  • ****
  • Posts: 312
  • Thanked: 103 times
Re: Excel's Random Generator Is Weak?
« Reply #5 on: August 13, 2015, 07:29:14 AM »
It's easy to get high quality random numbers from random.org. Just put  0 to 36 in the integer value fields.

They'll do me..
 

sqzbox

  • New
  • **
  • Posts: 60
  • Thanked: 5 times
Re: Excel's Random Generator Is Weak?
« Reply #6 on: August 14, 2015, 02:33:13 AM »
random.org is fine but there is a limit to how many you can generate at a time, and then you have to download them in a file and put them into a sheet, and all that carry-on. And then if you want to try a different million because you want to test against a different sample you have to do that all over again - it all becomes just too hard. Using a good RNG built-in to excel just makes life easier.