Random number generator expression used

My final year (Statistics degree) project is concerned 
with the PRNG in MS Excel.

It was stated by the lecturer in Simulation Studies that 
this PRNG was no good for simulation purposes .. the 
cycle length is not long enough.

So that I can test this statement I need to be able to 
run the in-built PRNG so that it repeats the sequence of 
generated random numbers.  This cannot be done in situ as 
there are not enough cells available (max 16k ?).

I would propose to run the PRNG as a VBA macro - or 
stand_alone in VB6 - but for that I need to obtain the 
mathematical expression that is used by Excel.

Can you tell me what it is, or where it may be published, 
or who to ask?
0
10/9/2003 8:10:55 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
975 Views

Similar Articles

[PageSpeed] 7

Roger  -

A long-ago knowledge base article said that Excel 4's RAND() is calculated
by multiplying 9821 times the previous value, adding 0.211327, and taking
the fractional part.

RAND() in more recent versions is likely different, and the one in Excel
2003 has been grreatly improved.

-  Mike Middleton, www.usfca.edu/~middleton


0
10/10/2003 2:38:54 AM
[This followup was posted to microsoft.public.excel.misc and a copy was 
sent to the cited author.]

How do you get a limit of 16K?  A *single* worksheet has 65536 x 256 
cells.  [Older versions of XL that had 16K rows would still support 16K 
* #columns cells on one worksheet.]

Also, keep in mind that XL's random number generator -- RAND() -- is 
unavailable in VBA, which has its own -- Rnd().

-- 
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office


In article <04b101c38ea1$7202a280$a401280a@phx.gbl>, 
roger.avery@students.plymouth.ac.uk says...
> My final year (Statistics degree) project is concerned 
> with the PRNG in MS Excel.
> 
> It was stated by the lecturer in Simulation Studies that 
> this PRNG was no good for simulation purposes .. the 
> cycle length is not long enough.
> 
> So that I can test this statement I need to be able to 
> run the in-built PRNG so that it repeats the sequence of 
> generated random numbers.  This cannot be done in situ as 
> there are not enough cells available (max 16k ?).
> 
> I would propose to run the PRNG as a VBA macro - or 
> stand_alone in VB6 - but for that I need to obtain the 
> mathematical expression that is used by Excel.
> 
> Can you tell me what it is, or where it may be published, 
> or who to ask?
> 
0
10/12/2003 2:38:12 AM
Roger,
Your instructor is on the right track. There are *three* distinct PRNGs 
in Excel: the worksheet function RAND(), the VBA funtion Rnd(), and the 
function used by the Analysis ToolPak, which is so bad that I'm sorta 
curious about how the heck it was implemented, and who was having issues 
with halucinagens when it was coded, and approved.

The cycle length is just the tip of the iceberg on problems with all 
three generators. As for the ATP version, there is no deterministic 
number for the cycle length that I can fing, independent of the seed 
value.
Try this: Use the ATP to generate 10,000 uniform variates in a column. 
Then sort them. Insert/Name/Define the range something like "MyRange" or 
"Bozo"; whatever. Then use the FREQUENCY function as follows:
select a column of cells as long as your data set, and ctrl-shift-enter
=FREQUENCY(Bozo,Bozo).  Copy / Paste-special(values) that data to 
another column, and sort it.  You will see how many values are repeated; 
I have a number of cases where some are repeated up to 8 times; less 
than half of the numbers are unique. It is *so* bad that I am pretty 
curious about the whole thing. Worse, it will generate both 0 *and* 1, 
which amazes me. Ideally, the generator avoids both values, though many 
reasonable ones generate o *or* 1, but never both. You can make a 
histogram of *that* histogram to summarize the appalling results.

For the worksheet RAND() function, it is vastly improved in Excel 11 
over earlier versions (which comprise at least 98% of used 
installations, by my guess).  The algorithm used has not been made 
public, yet. To check out its cycle length, as well as that for the VBA 
Rnd() function, just run a short VBA loop where you grab the first 
value, then to a DO-UNTIL loop which tests for an identical value. Might 
as well do it when you are away from the computer for a while, 
especially for testing RAND(), because you need to do a 
activesheet.calculate (be sure to do Application.Screenupdating = False 
before you start the loop.)

This is only the tip of the iceberg as regards Excel's unbelievably poor 
implementation of PRNG's. While the cycle lengths are amazingly short, 
there are many other problems; assuming Excel development started in 
1981, then the folks clearly chose to ignore the widely available, open 
source knowledge of the time. And We've learned a lot about PRNG since 
then; only in Excel 11 has *some* of that sunk in, and nopt throughout 
the product (such as the ATP).

For more insight, Google up Marsaglia, a prof over in Florida, who has 
written extensively about PRNG for many years. I don't think he has 
commented directly on Excel's functions, but he doesn't need to: they 
are widely known to be simply awful, in many dimensions.

HTH
Dave Braden
MVP - Excel
In article <04b101c38ea1$7202a280$a401280a@phx.gbl>,
 "roger Avery" <roger.avery@students.plymouth.ac.uk> wrote:

> My final year (Statistics degree) project is concerned 
> with the PRNG in MS Excel.
> 
> It was stated by the lecturer in Simulation Studies that 
> this PRNG was no good for simulation purposes .. the 
> cycle length is not long enough.
> 
> So that I can test this statement I need to be able to 
> run the in-built PRNG so that it repeats the sequence of 
> generated random numbers.  This cannot be done in situ as 
> there are not enough cells available (max 16k ?).
> 
> I would propose to run the PRNG as a VBA macro - or 
> stand_alone in VB6 - but for that I need to obtain the 
> mathematical expression that is used by Excel.
> 
> Can you tell me what it is, or where it may be published, 
> or who to ask?

-- 
(ROT13) qoenqra@zicf.bet
0
dbraden (33)
10/12/2003 5:31:07 AM
David: Thanks for the heads up on the ATP random generator.  I ran 10,000
numbers then sorted them.  I found 8 sets of duplicate
numbers in the the first 48 entries!  Wow.  Also thanks for the tip on Prof
Marsaglia.  I will check out his work.

Ken

David J. Braden <dbraden@real.address.below> wrote in message
news:dbraden-54B32C.22310711102003@msnews.microsoft.com...
> Try this: Use the ATP to generate 10,000 uniform variates in a column.
> Then sort them. Insert/Name/Define the range something like "MyRange" or
> "Bozo"; whatever. Then use the FREQUENCY function as follows:
> select a column of cells as long as your data set, and ctrl-shift-enter
> =FREQUENCY(Bozo,Bozo).  Copy / Paste-special(values) that data to
> another column, and sort it.  You will see how many values are repeated;
> I have a number of cases where some are repeated up to 8 times; less
> than half of the numbers are unique. It is *so* bad that I am pretty
> curious about the whole thing. Worse, it will generate both 0 *and* 1,
> which amazes me. Ideally, the generator avoids both values, though many
> reasonable ones generate o *or* 1, but never both. You can make a
> histogram of *that* histogram to summarize the appalling results.



0
kensch (23)
10/12/2003 6:58:58 PM
Ken,
Thanks for the thanks. I've a macro that I wrote that really brings this 
stuff out, but it didn't seem like it was worth clogging up bandwidth 
with the darn thing. Glad you got the idea. It seems to be not as well 
known, though developers within MS have been given the hard cold facts, 
along with an easy way to show them <veg>. To MS's credit, they made 
some strides in improving the stat functions when going from v 10 to v11 
*within* then worksheet; perhaps the ATP end of things will be fixed by 
v12. Believe me, I'm really working on it, much to the annoyance of many.

Dr. Marsaglia's stuff has really taken off in the last 17 years or so; 
he seems to have built up a neat cadre of support (starving PhD 
candidates, etc.), and the importance of his work is certainly fueled by 
the impact that computer-intensive Bayesian and non-parametric stat 
methods (such as bootstrapping) have taken on since about 1980, and how 
seriously useful they are, through a spreadsheet interface, to folks 
working high-end problems in insurance, pharma, petro-chem, 
portfolio-assessment, etc.. Hi,s and that of Ripley's, work is really 
cool (and of course that by Knuth) in this specialty. Given that it is 
out there, published, well-documented, and *free*, I take it as a 
slightly-more-than-bare minimum I hope to see MS implement. For another 
slant, check out the free PopTools for a very solid PRNG; site is
http://www.cse.csiro.au/poptools/
and its generator is seriously well tested and documented.

In contrast, the ATP is *so* pathological that I (hey, I *am* sick) am 
sometimes tempted to figure out just how the heck it could have been so 
badly messed up. I admit to have having nightmares trying to work 
through an algorithm that could produce what the ATP does wrt uniform 
variate generation.

Anyway, please *do* know that this particular issue has been 
communicated to MS since version 9 (at the latest); perhaps they might 
resolve it before I lose the rest of my hair.

Regards,
Dave Braden
MVP _ Excel

In article <Svhib.8515$dn6.1302@newsread4.news.pas.earthlink.net>,
 "Ken Schmidt" <kensch@ix.netcom.com> wrote:

> David: Thanks for the heads up on the ATP random generator.  I ran 10,000
> numbers then sorted them.  I found 8 sets of duplicate
> numbers in the the first 48 entries!  Wow.  Also thanks for the tip on Prof
> Marsaglia.  I will check out his work.
> 
> Ken
> 
> David J. Braden <dbraden@real.address.below> wrote in message
> news:dbraden-54B32C.22310711102003@msnews.microsoft.com...
> > Try this: Use the ATP to generate 10,000 uniform variates in a column.
> > Then sort them. Insert/Name/Define the range something like "MyRange" or
> > "Bozo"; whatever. Then use the FREQUENCY function as follows:
> > select a column of cells as long as your data set, and ctrl-shift-enter
> > =FREQUENCY(Bozo,Bozo).  Copy / Paste-special(values) that data to
> > another column, and sort it.  You will see how many values are repeated;
> > I have a number of cases where some are repeated up to 8 times; less
> > than half of the numbers are unique. It is *so* bad that I am pretty
> > curious about the whole thing. Worse, it will generate both 0 *and* 1,
> > which amazes me. Ideally, the generator avoids both values, though many
> > reasonable ones generate o *or* 1, but never both. You can make a
> > histogram of *that* histogram to summarize the appalling results.
> 
> 
>

-- 
(ROT13) qoenqra@zicf.bet
0
dbraden (33)
10/16/2003 12:08:18 AM
Reply:

Similar Artilces:

Auto-update Fill Series in column that has randomly spaced blank c
Hi, I have a worksheet of projects listed by quarter. A cell (say A6) contains QTR 1, followed by cells in column A filled with a series (1,2,3...say up to 12). Then comes a blank row followed by a row containing words "QTR 2" and then the fill series continues from where it left under QTR 1 i.e. 13,14,15...say up to 18. And so on for QTR 3 and QTR 4. When a new project is added, I want to be able to add a row and with minimal steps, want the fill series below this added new project, to update. When I delete a row (project), I want the fill series to automatically ...

GetOpenFileName + Using own dialog template
Hi. I have a question: I am using the GetOpenFileName function to display the Windows FileDialog. I want to use the EXPLORER functionality and therefore I declared the flag OFN_EXPLORER in the OPENNAME-structure. Now here is the problem: I want to use my own dialog-template instead of the standard-file-open dialog. So i wrote my own template and set the attribute templateName of the OPENNAME structur to my own template. Furthermore i activated the flag OFN_ENABLETEMPLATE. But when I call the GetOpenFileName function with these properties I get the errorcode CDERR_GENERALCODES and the dialog is...

Using cell reference with logical operator in DGET expression
I am using DGET to search an array in the worksheet to find a particular percent to use in a calculation elsewhere in the spreadsheet. The row members of the array contain a series of from and to values that I use to identify which row has the percent I am looking for. I have defined the array as a range and I can use the logical operatirs with numeric values in the range criteria and everything works fine. For example, I can use <500 in the appropriate cell in range criteria to find the percent to use when the value for that column in the array is less than 500. All working f...

Continuous Form Use
I created a form similar to an Access form. It's for logging in phone calls and messages. Is there a way to set it up like in Access where a single form appears on 1 page but can generate subsequent pages within the file? Or is setting it up as a table the only way it can be done? Our IT contractor doesn't want me to use Access otherwise this would be a breeze. Any suggestions or help is appreciated. Thanks. You can spawn forms off a form, and access the data on that form. -- HTH RP (remove nothere from the email address if mailing direct) "ryjack" <ryjack@disc...

Comments using Validation input
A few days ago a question relating to comments grabbed my attention. The reply informed us that the comment in question was able to be seen by clicking the cell it was attached to and as long as that cell remained active the comment could be seen. So we have a comment which is not a comment visible only when the cell is selected as opposed to a comment which is visible when the cursor is over the cell. Sounds confusing but it achieves the result of showing a "floating" comment which remains in the same place on the screen even when it applies to a cell in a 'frozen' section a...

Receivable transactions using Integration Manager
Hi, I'm new to Integration manager and while trying to import receivable transactions of type "invoice" into Great Plains using Integration Manager, I receive the following errors: ERROR: Field 'Customer ID' does not have a default value. ERROR: Field 'Distribution.Distribution Account' does not have a default value. I am using a tab delimited text file for import. Customer Id is of the format 000001 Distr. Account is of the format 9999-999-99-99-99 Can anyone help me with this. Check each one of your mapping and make sure it says 'use source' an...

How to repalce numbers after a decimal
Lets say I have a column of numbers, that looks like this: 10.242 11.817 12.915 14.805 and I want it to look like this: 10.95 11.95 12.95 14.95 What function could be used to accomplish this? Format->Cells->Number tab->Click 'Custom', and enter this: 0.95 -- Regards, Dave "dpscoupe" wrote: > Lets say I have a column of numbers, that looks like this: > > 10.242 > 11.817 > 12.915 > 14.805 > > and I want it to look like this: > > 10.95 > 11.95 > 12.95 > 14.95 > > What function could be used to accomplish thi...

how i can change default file format Outlook uses to save message
I my saving message in my inbox using "File->Save As" menu. "Save As.." dialog by default shows "Save As Type:" as "HTML". How I can change default file format Outlook uses to save messages to as "Outlook Message Format - Unicode" ?????? Rajkumar <Rajkumar@discussions.microsoft.com> wrote: > I my saving message in my inbox using "File->Save As" menu. > "Save As.." dialog by default shows "Save As Type:" as "HTML". > How I can change default file format Outlook uses to save messages...

External data query using MAX/SUM
I'm trying to create an external data link using Microsoft Query. Ho do I write SQL in Microsoft query that will return a max date and su an amount field for the max date only? Current SQL is as follows: SELECT DISTINCT CB_PORTFOLIO_DMN.PORTFOLIO_NAME Max(CB_POOL_PORTFOLIO.POST_DATE), Sum(CB_POOL_PORTFOLIO.EXPOSURE_AMT) Count(CB_POOL_PORTFOLIO.NBR_AVG_MATY_YRS) FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO, CB06U.CB_PORTFOLIO_DM CB_PORTFOLIO_DMN WHERE CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID GROUP BY CB_PORTFOLIO_DMN.PORTFOLIO_NAME This returns the sum of the exp...

Mirror a number
I am trying to mirror a number in a cell. For example, I want the product of 10 * 5 to equal 05 or at least be able to take a number like 1234 and change it to 4321 automatically. Please help me! Hi Steve try the following UDF (not much checking, etc. included): Function mirror(in_value) As Double Dim c Dim ret For c = Len(CStr(in_value)) To 1 Step -1 ret = ret & Mid(CStr(in_value), c, 1) Next mirror = CDbl(ret) End Function Use it like the following in your worksheet: =mirror(12345) or =mirror(A1) Note: mirror(5*10) would return '5' as the leading '0' is skipped ...

List of fonts used in a Word doc -- the easy way
The VBA scripts that I have found all implement a character-by-character scan throu= gh the document, which can take a long time in later versions of Word. You can blame Wor= d's "formatting" feature for the slowdown. There=E2=80=99s a faster way: Save the document in .rtf format, then open the .rtf fi= le in a text editor such as notepad.exe. The fonts are listed in a font table ( {\fonttb= l ) located at the very beginning of the file. Each font entry looks something like thi= s: = {\f0\froman\fprq2\fcharset0 Times New Roman;} Any entries with a character set ...

Convert number pad decimal to comma decimal separator
Hi, One of our european users has asked if we could add a feature to our app that would interpret a number pad decimal keystroke as a comma decimal separator. Whilst I could subclass the various number entry controls, dialog, grid, currency, plain number ect, I was hopeful that a application wide method could be found, utilising WM_KEYUP / WM_KEYDOWN. I have looked at using ProcessMessageFilter, but not had much success. Does anyone know how this may be acheived? Many thanks, Mike Stoodsmj wrote: > One of our european users has asked if we could add a feature to our > app that w...

sending email from outlook using AOL
After much exasperation have finally got outlook to send email whilst using my AOL internet connection. The issue is that I have to have my aol email address entered in the Outlook email box but I use my outlook email address in the reply email address box. The problem is that any recipient of an email from outlook will see my aol email address in the from box and if they click on reply it will be sent to my aol email box and not to my outlook email box. If my aol email address is not entered, in the Outlook email box, the email is rejected by aol. Using AOL 9 & Outlook 2002 Any ...

Music files using "Package for CD" Option in Powerpoint
I am trying to burn my show onto a disk, and the show has songs linked to it. I know the songs are not embedded into the show, so I was looking for a solution for this. I saw the "package for CD" option in the menu and according to that, it is supposed to take your show, and all linked files and burn it onto a disk to be played back using powerpoint viewer. I made sure the box was checked to include linked files. After the disk burned, no music. I tried putting all the music and show in one folder and then running the package for cd tool, still no music. Is ther...

Conditional Summing across ranges using arrays
Hi all - I'm struggling to find a formula that will solve my problem: I have a matrix of numbers defined by a series of numerical column and row headers. RefCel 1 2 3 4 5 . . . m 1 a # # # # 2 # # # # # .. 5 b # # # # .. n # # # # # What I'm trying to do is for a give column header and a subset of row headers, sum the intersecting values. Let's assume that the subset are held in a named range Subset and the row and column headers are in range called RowHead and ColHead and...

row numbers & column letters
How do I print the row numbers but not the column letters? Hi AFAIK you can't do this -- Regards Frank Kabel Frankfurt, Germany ALOlson21 wrote: > How do I print the row numbers but not the column letters? Hi AFAIK, I would just move your data over and in the A column use the formula -- =row() That will put the row number in the cell. Then you can copy and paste that formula down your sheet, and if you want put a border around those cells to dress it up a little. Does that help? "Frank Kabel" wrote: > Hi > AFAIK you can't do this > > -- > Re...

Outlook Express downloads all email to one family user
I've set up a new PC. All of my Epxress POP3 and SMTP information is entered correctly, and I have entered five email addresses into Express (one Main account and four others) My problem concerns receiving email. When logging into Express, all undelivered email for all five accounts is downloaded to the first account Express opens. For example, there are six pieces of underlivered email this morning -- two addressed to the "Dad" account and four addressed to the "Mom" account. Child 1 opens Express and signs into his account and the six emails are downloaded to the...

Using a txt string in formulae
Right this is an anoying little beggar but probably simple and im failing to see the wood from the trees so to speak ! right i have a workbook with multiple sheets and a front sheet the front sheet reports results of counts from the other sheets using =COUNT(sheet1!A:A) and =COUNTIF(Sheet1E:E,"Neg room") and other similair formula my question is this In column A i have the name of the sheet so I.E. A1=sheet1 which hyperlinks to the named sheet and columb B has the first formulae and so on and so on! my question is is there a way i can add the Word name of the sheet the Count form...

how do i autofill without using mouse?
how do i autofill without using mouse? anyone here know the shortcut for this? thank -- Message posted from http://www.ExcelForum.com Alt + E + I +S & Enter for 1, 2 etc.. -- Regards, Peo Sjoblom "sokevin >" <<sokevin.10x964@excelforum-nospam.com> wrote in message news:sokevin.10x964@excelforum-nospam.com... > how do i autofill without using mouse? > > anyone here know the shortcut for this? > > > thanks > > > --- > Message posted from http://www.ExcelForum.com/ > ...

Error when printing account numbers
Using Dyamics 9, whenever we try to print something out of Dynamics the account numbers don't print correctly. The numbers aren't what they should be and it prints squares instead of some numbers. For example, instead of printing 10-13400-000-0000 it prints 10-400X0- X0- (with squares in place of X's). This occurs anywhere we try to print within Dynamics, but doesn't occur when we use Frx to create reports. Additionally not every computer has this problem, but we can't find any difference between the computers that would cause this. Check the fonts assigned on ...

Microsoft Publisher 2002
All of a sudden I can no longer even open Microsoft Publisher on my son's computer. Recently there was an Windows update that referred to program authentication. We had installed this publisher program on this computer after our previous computer crashed but we only have it running on one computer so this is very frustrating! I have tried reinstalling the program to no avail. I would be extremely grateful if there was a solution to this problem. Use the phone activation and explain. 888-652-2342 (Not sure this number is still valid) Was the program installed when you bought...

music in Outlook Express
I can't seem to embed music in my Outlook Express 6 email. I had no problems with my older computer, I just went to format, background and music. But on my new laptop I can't get the music to go into the email. Not only that but when I hit send it says it can't send because it the picture can't be found. I'm not even sending a picture. gees.. whats up with this? Reply to Pur2softly@mwic.net because I'm not sure I will be able to find this newsgroup back again. Thank you. Ask in an Outlook Express forum. This is an Microsoft Office Outlook forum. Outlook Expre...

editing cells without using mouse
Hi, I try to use the mouse as little as possible, and I can navigate in the spreadsheets just fine with the arrows on the keyboard, but I haven't found a way to edit a cell, e.g. insert some $-signs in a long formula, without mouseclicking the formula-bar above the sheet. I can type in the whole formula once more using only the keyboard of course, but that is not a very simple way to go if the formula is long and hard to remember. Anyone know how to edit the contents of a cell using only the keyboard? Thomas F -- thomasF --------------------------------------------------------------...

Dispose of ADODB.Recordset with VB Express 2008
Hi, I've used the VB Express upgrade feature to transform code from VB6 to VB Express 2008 Part of my original code included: <Code> Dim RS As New ADODB.Recordset Set RS = Nothing RS.Open("SELECT DISTINCT GENUS FROM Plants ORDER by GENUS;", ogAccess) </Code> The translated code included: <Code> 'UPGRADE_NOTE: Object RS may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"' RS= Nothing </Code...

Sum of mixed leters and numbers.
Hello I am trying to add sums in a table that contain both numbers and letters, such as PRPT0 8BAAA QL1TO these are listed multiple times. I am searching for a formula that will extract the amount of times each one is used in a cell. I would appreciate any help people are willing to give. Hi if you want the count of the entire cell content try =COUNTIF(A:A,"PRPT0") or try creating a pivot table for this >-----Original Message----- >Hello I am trying to add sums in a table that contain both numbers and letters, such as > > >PRPT0 >8BAAA >QL1TO > >thes...