Recording incidences of results of a RAND() fucntion.

[BaldySlaphead]

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

[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

BaldySlaphead wrote:
>
> 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
>
> --
> BaldySlaphead
> ------------------------------------------------------------------------
> BaldySlaphead's Profile: http://www.excelforum.com/member.php...fo&userid=1260
> View this thread: http://www.excelforum.com/showthread...hreadid=387444

--

Dave Peterson