I'm testing random numbers for games using excel 07 generator, but there is
too much bias away from statistical expectation. For example, when two
integer numbers are generated between 1 and 6, and subsequently added, the
results differ from the easily predictable numbers. Sevens are consistently
in short supply by about 1.5%. That's a lot! Any suggestions for better
randomization in excel would be very much appreciated.
--
yttrias

Here are my results from excel 2003

1 1652
2 1715
3 1689
4 1668
5 1661
6 1615



Sub countrand()

Dim numbers(6)

For i = 1 To 6
numbers(i) = 0
Next i

For i = 1 To 10000
myRand = Int(6 * Rnd()) + 1
numbers(myRand) = numbers(myRand) + 1
Next i

For i = 1 To 6
Range("A" & i) = i
Range("B" & i) = numbers(i)
Next i

End Sub


"yttrias" wrote:

> I'm testing random numbers for games using excel 07 generator, but there is
> too much bias away from statistical expectation. For example, when two
> integer numbers are generated between 1 and 6, and subsequently added, the
> results differ from the easily predictable numbers. Sevens are consistently
> in short supply by about 1.5%. That's a lot! Any suggestions for better
> randomization in excel would be very much appreciated.
> --
> yttrias

And what was your sample size?
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"yttrias" <(E-Mail Removed)> wrote in message
news:09EA2A85-AE22-4CB1-9537-(E-Mail Removed)...
> I'm testing random numbers for games using excel 07 generator, but there
> is
> too much bias away from statistical expectation. For example, when two
> integer numbers are generated between 1 and 6, and subsequently added, the
> results differ from the easily predictable numbers. Sevens are
> consistently
> in short supply by about 1.5%. That's a lot! Any suggestions for better
> randomization in excel would be very much appreciated.
> --
> yttrias

On Dec 16, 2:22 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> And what was your sample size?
> --
> Bernard V Liengme
> Microsoft Excel MVPwww.stfx.ca/people/bliengme
> remove caps from email
>
> "yttrias" <yttr...@discussions.microsoft.com> wrote in message
>
> news:09EA2A85-AE22-4CB1-9537-(E-Mail Removed)...
>
> > I'm testing random numbers for games using excel 07 generator, but there
> > is
> > too much bias away from statistical expectation. For example, when two
> > integer numbers are generated between 1 and 6, and subsequently added, the
> > results differ from the easily predictable numbers. Sevens are
> > consistently
> > in short supply by about 1.5%. That's a lot! Any suggestions for better
> > randomization in excel would be very much appreciated.
> > --
> > yttrias

The flaw in the MS random number generator has been known for some
time. A description is included in this MS info sheet:

http://support.microsoft.com/kb/829208

I use a simulation package that has it's own generator. But there are
probably others you can download via a web search.

You may want to run statistical tests on ones that you are considering
for validation.

SteveM

But http://support.microsoft.com/kb/828795/ suggests RAND is OK in XL 2003
and 2007
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"SteveM" <(E-Mail Removed)> wrote in message
news:c60e5bec-1bbf-4f5b-9763-(E-Mail Removed)...
> On Dec 16, 2:22 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
> wrote:
>> And what was your sample size?
>> --
>> Bernard V Liengme
>> Microsoft Excel MVPwww.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "yttrias" <yttr...@discussions.microsoft.com> wrote in message
>>
>> news:09EA2A85-AE22-4CB1-9537-(E-Mail Removed)...
>>
>> > I'm testing random numbers for games using excel 07 generator, but
>> > there
>> > is
>> > too much bias away from statistical expectation. For example, when two
>> > integer numbers are generated between 1 and 6, and subsequently added,
>> > the
>> > results differ from the easily predictable numbers. Sevens are
>> > consistently
>> > in short supply by about 1.5%. That's a lot! Any suggestions for
>> > better
>> > randomization in excel would be very much appreciated.
>> > --
>> > yttrias
>
> The flaw in the MS random number generator has been known for some
> time. A description is included in this MS info sheet:
>
> http://support.microsoft.com/kb/829208
>
> I use a simulation package that has it's own generator. But there are
> probably others you can download via a web search.
>
> You may want to run statistical tests on ones that you are considering
> for validation.
>
> SteveM

yttrias -

Which "excel 07 generator" are you using?

RAND() worksheet function, or

RND function in VBA, or

Excel 2007, Data ribbon, Data Analysis, Random Number Generation tool ?

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel



"yttrias" <(E-Mail Removed)> wrote in message
news:09EA2A85-AE22-4CB1-9537-(E-Mail Removed)...
> I'm testing random numbers for games using excel 07 generator, but there
> is
> too much bias away from statistical expectation. For example, when two
> integer numbers are generated between 1 and 6, and subsequently added, the
> results differ from the easily predictable numbers. Sevens are
> consistently
> in short supply by about 1.5%. That's a lot! Any suggestions for better
> randomization in excel would be very much appreciated.
> --
> yttrias

I ran 1 million samples using RAND in VBA in excel 07. Then I sampled 40,000
consecutive values at 20 different locations throughout the range of 1M. The
average of those 20 windows in the 1M was 1587, where the expected average
should be 1667.
--
yttrias


"Bernard Liengme" wrote:

> And what was your sample size?
> --
> Bernard V Liengme
> Microsoft Excel MVP
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "yttrias" <(E-Mail Removed)> wrote in message
> news:09EA2A85-AE22-4CB1-9537-(E-Mail Removed)...
> > I'm testing random numbers for games using excel 07 generator, but there
> > is
> > too much bias away from statistical expectation. For example, when two
> > integer numbers are generated between 1 and 6, and subsequently added, the
> > results differ from the easily predictable numbers. Sevens are
> > consistently
> > in short supply by about 1.5%. That's a lot! Any suggestions for better
> > randomization in excel would be very much appreciated.
> > --
> > yttrias
>
>
>

I don't know about the OP, but Joel's posted code used the VBA Rnd()
function. Prior to 2007, there were 3 distinct random number generators, the
worksheet function RAND(), VBA Rnd(), and the ATP random number generator.
None were very good before 2003, but their problems would arise as high order
autocorrelations between "random" numbers not as the kind of problems the
Joel was testing for. It is unlikely to show up in the process that the OP
seems to describe. Therefore I would want to know how the OP generated
"integer numbers between 1 and 6" and what the OP was using as expected
values.

A decent, but not great algorithm was implemented for worksheet RAND()
(requires SP1 patch to work properly) and carried into 2007. I have seen no
evidence that VBA Rnd() has ever been upgraded. RANDBETWEEN() became a
worksheet function in 2007, and therefore probably switched from using ATP
random number generator to using the worksheet RAND() function, though I have
seen no confirmation of this.

Joel's simulation shows departures from expectation that are NOT
statistically significant. Generating 10000 sums of 2 values using worksheet
RAND() and expected values of (6-ABS(7-tot2))/36, I tried several reps in
2003 with no statistically significant departure from expectation. The
following VBA code does 10 reps of 10000 using VBA Rnd(), and only one rep
was statistically different from expectation (again in 2003). Therefore, I
am somewhat skeptical of the OP's claims until more information is provided.

Jerry

Sub tryit()
Application.ScreenUpdating = False ' to speed execution
For j = 1 To 10
For i = 1 To 10000
x = Fix(Rnd() * 6) + Fix(Rnd() * 6) + 2 ' sum of two random
integers between 1 and 6
Cells(x, j) = Cells(x, j) + 1
Next i
Next j
Application.ScreenUpdating = True
End Sub


"Mike Middleton" wrote:

> yttrias -
>
> Which "excel 07 generator" are you using?
>
> RAND() worksheet function, or
>
> RND function in VBA, or
>
> Excel 2007, Data ribbon, Data Analysis, Random Number Generation tool ?
>
> - Mike Middleton
> http://www.DecisionToolworks.com
> Decision Analysis Add-ins for Excel
>
>
>
> "yttrias" <(E-Mail Removed)> wrote in message
> news:09EA2A85-AE22-4CB1-9537-(E-Mail Removed)...
> > I'm testing random numbers for games using excel 07 generator, but there
> > is
> > too much bias away from statistical expectation. For example, when two
> > integer numbers are generated between 1 and 6, and subsequently added, the
> > results differ from the easily predictable numbers. Sevens are
> > consistently
> > in short supply by about 1.5%. That's a lot! Any suggestions for better
> > randomization in excel would be very much appreciated.
> > --
> > yttrias
>
>
>

You are still providing too little information for us to understand what you
are doing.

There is no RAND() function in VBA. In VBA, you either used the VBA Rnd()
function, or the worksheet RAND() function via Evaluate("RAND()").
As noted in my earlier post, in 2003 the worksheet RAND() function was far
superior to the VBA Rnd(). I have seen no evidence that either function
changed between 2003 and 2007.

Your original post discussed the sum of two (presumably independent) random
integers between 1 and 6 (such as the total from rolling two fair dice). The
expected value of a single such sum would be 7. It is unclear how that
relates to either your observed average of 1587 or expected average of 1667
for an "average of 20 windows of 40,000 consecutive values.

It sounds as though your VBA would involve only a few lines of code. If you
paste that code into your reply, we can see exactly what you did.

Jerry

"yttrias" wrote:

> I ran 1 million samples using RAND in VBA in excel 07. Then I sampled 40,000
> consecutive values at 20 different locations throughout the range of 1M. The
> average of those 20 windows in the 1M was 1587, where the expected average
> should be 1667.
> --
> yttrias
>
>
> "Bernard Liengme" wrote:
>
> > And what was your sample size?
> > --
> > Bernard V Liengme
> > Microsoft Excel MVP
> > www.stfx.ca/people/bliengme
> > remove caps from email
> >
> > "yttrias" <(E-Mail Removed)> wrote in message
> > news:09EA2A85-AE22-4CB1-9537-(E-Mail Removed)...
> > > I'm testing random numbers for games using excel 07 generator, but there
> > > is
> > > too much bias away from statistical expectation. For example, when two
> > > integer numbers are generated between 1 and 6, and subsequently added, the
> > > results differ from the easily predictable numbers. Sevens are
> > > consistently
> > > in short supply by about 1.5%. That's a lot! Any suggestions for better
> > > randomization in excel would be very much appreciated.
> > > --
> > > yttrias

>I ran 1 million samples using RAND in VBA in excel 07. Then I sampled
>40,000
> consecutive values at 20 different locations throughout the range of 1M.
> The
> average of those 20 windows in the 1M was 1587, where the expected average
> should be 1667.

How are you using th Randomize statement? If you are executing it more than
one time, that could be skewing your results. Consider this posting I have
posted in the past...

The Randomize statement should not be executed more than once for the
lifespan of the object that is executing your code. Doing so more often
actually makes the data less random than if Randomize is only run once. For
so few colors, and the probable use the OP wants to use the Rnd function
for, it will more than likely not matter here, but it is the concept that is
important to know. I can't demo it within Excel because I am not aware of an
available object where I can set the color of individual pixels (like a
PictureBox control in the compiled version of VB for those who have worked
with that language); however, I do have a VB program that demonstrates this
fact visually.

For those of you having access to the compiled versions of VB5 or VB6, here
is posting I have offered over in the compiled VB newsgroups in the past
that demonstrates this fact...

Running Randomize multiple times ends up producing a less random set of
numbers. To see the problem visually, use this code (which is a modification
of a routine Bob Butler once posted). Start a new project and put two
PictureBox'es on your Form (use the default names for everything and
placement of the PictureBox'es is not important). Paste the following code
into the Form's code window. The PictureBox on the left results from using
Randomize only once, the one on the right uses it repeatedly. Both
PictureBox displays are produced from the same looping code with the only
difference being the use of the Randomize statement Ignoring the pronounce
vertical areas (not sure what that is, probably some kind of boundary
rounding problem), for which one does the distribution of colors look more
"random"?

Rick

Const SCALESIZE = 3

Private Sub Form_Load()
Picture1.ScaleMode = 3
Picture2.ScaleMode = 3
Randomize
Picture1.Move 0, 0, _
128 * Screen.TwipsPerPixelX * SCALESIZE, _
128 * Screen.TwipsPerPixelY * SCALESIZE
Picture2.Move Picture1.Width, 0, _
128 * Screen.TwipsPerPixelX * SCALESIZE, _
128 * Screen.TwipsPerPixelY * SCALESIZE
Me.Width = 2.02 * Picture1.Width
Me.Height = 1.1 * Picture1.Height
End Sub

Private Sub Picture1_Paint()
Dim i As Long
Dim j As Long
Dim colr As Long
Dim bitmask As Long
For i = 0 To Picture1.ScaleHeight Step SCALESIZE
For j = 0 To Picture1.ScaleWidth Step SCALESIZE
colr = Rnd * 16711680
Picture1.Line (j, i)-Step(SCALESIZE, _
SCALESIZE), colr, BF
Next j
Next i
For i = 0 To Picture2.ScaleHeight Step SCALESIZE
For j = 0 To Picture2.ScaleWidth Step SCALESIZE
Randomize
colr = Rnd * 16711680
Picture2.Line (j, i)-Step(SCALESIZE, _
SCALESIZE), colr, BF
Next j
Next i
End Sub