Recording incidences of results of a RAND() fucntion.

[BaldySlaphead]

I have a random function which generates an integer from 1-9 with eac
screen refresh.

Would I be able to tally how many times each number was generated? Wha
I imagined was setting up an additional COL 'Results' and then trying t
increment an

-if [Cell Ref of Rand Function]=1 then x (cell containing tally o
incidence of result 1) =x+1

if [Cell Ref of Rand Function]=2 then x (cell containing tally o
incidence of result 2) =x+1 - etc. etc.

style argument. Thus I could see how often a number was picked by th
RAND.

Is this possible, and how might I do it? Any comments gratefull
received.

Thanks for looking!

Bald

 

[Dave Peterson]

You could use a worksheet event that checks that cell after every recalculation,
but that seems like you could lose control pretty quickly (excel can recalculate
lots of times and you may not be prepared for that).

I think I would use a dedicated macro.

I put =randbetween(1,10) in A1 (I had to have the analysis pack addin loaded)

Then I had used this macro to count the results.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim iCtr As Long
Dim MaxTimes As Long

MaxTimes = 10

With Worksheets("Sheet1")
Set myCell = .Range("a1")
Set myRng = .Range("b1:B10") '<- ten cells!

myRng.ClearContents 'start new each time?
For iCtr = 1 To MaxTimes
Application.Calculate
myRng(myCell.Value) = myRng(myCell.Value) + 1
Next iCtr
End With

End Sub

If calculation is set to automatic, just incrementing the cell causes a recalc.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

I have a random function which generates an integer from 1-9 with each
screen refresh.

Would I be able to tally how many times each number was generated? What
I imagined was setting up an additional COL 'Results' and then trying to
increment an

-if [Cell Ref of Rand Function]=1 then x (cell containing tally of
incidence of result 1) =x+1

if [Cell Ref of Rand Function]=2 then x (cell containing tally of
incidence of result 2) =x+1 - etc. etc.

style argument. Thus I could see how often a number was picked by the
RAND.

Is this possible, and how might I do it? Any comments gratefully
received.

Thanks for looking!

Baldy