RandBetween Number Generator For Statistics

adamfoster · Nov 7th 2010

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!
Adam

adamfoster · Nov 7th 2010

Re: Recalculating a worksheet and saving the results

1

**S M C** · Nov 7th 2010

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

Code

Sub Calc1000()


    Dim lngLoop As Long
    
    For lngLoop = 1 To 10000
        Worksheets(3).Range("A1").Calculate
        Worksheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets(3).Range("A1")
    Next lngLoop
    
    lngLoop = Empty
    
End Sub

Display More

adamfoster · Nov 7th 2010

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!!!

**S M C** · Nov 7th 2010

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

adamfoster · Nov 7th 2010

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!

**S M C** · Nov 7th 2010

Re: Recalculating a worksheet and saving the results

Let me see what I can make up.

**S M C** · Nov 7th 2010

Re: Recalculating a worksheet and saving the results

Code

Sub RandomGenerator()


    Const lngRowsInOneSet = 4000
    Const lngCountOfSets = 10000
    Const lngLowerValue = 1
    Const lngHigherValue = 1000
    Const lngCriteria = 34
    
    Dim lngTrueCounter As Long
    Dim lngLoop01 As Long
    Dim lngLoop02 As Long
    Dim lngLoop03 As Long
    
    For lngLoop02 = 1 To lngCountOfSets
        For lngLoop01 = 1 To lngRowsInOneSet
            lngLoop03 = lngLoop03 + 1
            Application.StatusBar = lngLoop03 & "/" & lngRowsInOneSet * lngCountOfSets
            If Int(Rnd() * (lngHigherValue - lngLowerValue + 1) + lngLowerValue) < lngCriteria Then
                lngTrueCounter = lngTrueCounter + 1
            End If
        Next lngLoop01
        With Worksheets(1)
            .Range("A" & .UsedRange.Count).Value = lngTrueCounter
        End With
        lngTrueCounter = Empty
    Next lngLoop02
    
    Application.StatusBar = False
    lngLoop01 = Empty
    lngLoop02 = Empty
    
End Sub

Display More

adamfoster · Nov 7th 2010

Re: Recalculating a worksheet and saving the results

ok, thanks!

adamfoster · Nov 7th 2010

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"

**S M C** · Nov 7th 2010

Re: RandBetween Number Generator For Statistics

Code

Sub RandomGenerator()


   Const lngRowsInOneSet = 400
   Const lngCountOfSets = 1000
   Const lngLowerValue = 1
   Const lngHigherValue = 1000
   Const lngCriteria = 34
   
   Dim lngTrueCounter As Long
   Dim lngLoop01 As Long
   Dim lngLoop02 As Long
   Dim lngLoop03
   
   For lngLoop02 = 1 To lngCountOfSets
       For lngLoop01 = 1 To lngRowsInOneSet
           lngLoop03 = lngLoop03 + 1
           Application.StatusBar = lngLoop03 & "/" & lngRowsInOneSet & "x" & lngCountOfSets
           If Int(Rnd() * (lngHigherValue - lngLowerValue + 1) + lngLowerValue) < lngCriteria Then
               lngTrueCounter = lngTrueCounter + 1
           End If
       Next lngLoop01
       With Worksheets(1)
           .Range("A" & .UsedRange.Count).Value = lngTrueCounter
       End With
       lngTrueCounter = Empty
   Next lngLoop02
   
   Application.StatusBar = False
   lngLoop01 = Empty
   lngLoop02 = Empty
   
End Sub

Display More

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

adamfoster · Nov 7th 2010

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!

**S M C** · Nov 7th 2010

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.

Rob Xaos · Nov 8th 2010

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.

RandBetween Number Generator For Statistics

Participate now!

Share

Users Viewing This Thread