 # RandBetween Number Generator For Statistics

• Hello,

I am new to trying to programming with excel. I am trying to gather some statistics on random numbers generated by a worksheet. Here is what I would like to do:

I would like to be able to use one sheet to call on another sheet to recalculate itself a specified number of times (up to 10,000) and after each recalculation to save one of the cells on the recalculating sheet.

In other words, In the 1st worksheet I would get 10,000 cells of the results of the 2nd worksheet calculation that I could then run some simple statistics on.

Is this possible? Is there a simple way to do this? I have very little experience!

Thanks for the help!

• Re: Recalculating a worksheet and saving the results

1

• Re: Recalculating a worksheet and saving the results

Why don't you just use the formula =RAND() and drag it all the way down from cell 1 to cell 10000. This will generate 10000 random numbers.

On the other hand, if you want to force calculation on Sheet(3), and get the value populated 10000 times in Sheet(2), you could use

Cheers,

S M C

• Re: Recalculating a worksheet and saving the results

Thanks,

Yeah, I can generate the random numbers no problem.
On sheet 2 I am generating 100000 random numbers between 1 and 1000 then counting how many of them are below 34. what I want to do is save this value and then generate another 100000 on sheet 2 and redo the count and save about 10000 times so I can run some statistics on.

How would I use the code you supplied? I am a very beginner, I know where to enter the code, but once it's in there, then I don't really know how to use it.

Thank you!!!

• Re: Recalculating a worksheet and saving the results

Well, an easy way to do this (if it is just a one time activity). Write =RANDBETWEEN(1,1000) in Range A1 in Sheet 2, and drag that formula All the way down to 100000 rows and all the way across to 10000 columns.

And in the 100001th row, in column A, ie, Range("A100001") put the formula =COUNTIF(A1:A100000,"<34"), and drag this across to the 10000 columns. You now have counts of numbers less than 34, 10000 times.

The copy this last row, and paste special transpose values to the other sheet. You now have a vertical column of 10000 numbers showing the count of numbers less than 34

Cheers,

S M C

• Re: Recalculating a worksheet and saving the results

but if I want to be able to change the number 100000 to as little as 4000 and see the results?

That is instead of counting the number of 100000 random #'s Between 1and 1000 are less than 34 10,000 times I wanted to count the number of 4000 random #'s less than 34 10,000 times.

Basically, This is for an educational simulation so I want to be able to change the number of cycles and have the data show up in real time to illustrate a point.

Thanks for helping with this, I'm sorry if I am not providing clear questions!

• Re: Recalculating a worksheet and saving the results

Cheers,

S M C

• Re: Recalculating a worksheet and saving the results

ok, thanks!

• Re: Recalculating a worksheet and saving the results

Thanks for that, that was quick

unfortunately, when I tried to run this it came up with a dialog box saying "overflow"

• Re: RandBetween Number Generator For Statistics

I have reduced the numbers by a tenth. Try gradually incrementing.

Cheers,

S M C

• Re: Recalculating a worksheet and saving the results

Hey, thanks for all your help.

I was able to manipulate your 1st code that you gave me in order to get my program to do what I wanted, more or less with some minor changes.

Thank you!

• Re: RandBetween Number Generator For Statistics

Nice. Also notice the change I made to the thread title. Next time you start a new thread, take more care to develop a more carefully worded title. The title should be as concise as possible while accurately describing your need/question, and not what you think is the solution. The key to proper thread titles is to think "search friendly", which means a search using your title as the search terms would yeild relevant results.

Cheers,

S M C

• Re: RandBetween Number Generator For Statistics

It is unclear to me why you would want to run such basic tests on the Excel random function. When it was revised several years ago by Microsoft, extensive testing was carried out using the DIEHARD series of tests as well as additional tests from NIST.

If you want more details about the algorithm used and the testing it underwent then see: http://support.microsoft.com/kb/828795

The kind of tests you are describing in this thread do not provide any real analyis of the quality of the RANDBETWEEN function.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!