Wrong coding or RANDBETWEEN is wrong?

I've try to solve this question: http://www.excelforum.com/excel-prog...32#post3340932

Now, first I've used solution with RANDBETWEEN function:

Please Login or Register  to view this content.

However, I got bad distribution:

Please Login or Register  to view this content.

With this approach it looks fine:

Please Login or Register  to view this content.

And result is:

Please Login or Register  to view this content.

Is there a my fault or really RANDBETWEEN doesn't work as expected?

Here I've extend range of first code...
Pattern is obvious

Rnd.png

I'd prefer:

Please Login or Register  to view this content.

@zbor

What you record is the frequency of the combination of randbetween 1 to 6 and randbetween 1 to 3.
That's quite another thing than the randomization for 1 to 18.

If you do the randomization for 1 to 18 in your code the frequency for every cell is almost identical.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

With 180,000 selections of 18 cells, you'd expect the number in each cell to be close to 1000 -- and indeed, z1 does that, e.g,

Please Login or Register  to view this content.

If you replace Rnd() with [rand()], it gives a similar result (just more slowly).

Surprisingly, though, this is a typical output for z2:

Please Login or Register  to view this content.

I may be missing something obvious, but that is impossibly far away from uniform distribution. I'd have thought that RANDBETWEEN used RAND() internally, but apparently not.

I must be missing something obvious. When I do it sans code (a column of RB(1,6) and a column of RB(1,3)), it's very uniform.

Originally Posted by shg

I may be missing something obvious, but that is impossibly far away from uniform distribution. I'd have thought that RANDBETWEEN used RAND() internally, but apparently not.

Thx for jumping in...
I've tried to test it last night a little bit and it seems that Rand and Randbetween works fine unless they are used in Cells.

Those are two codes:

First creates 3 tables with Rand function:
1. Cells(static, random)
2. Cells(random, static)
3. Cells(random, random)

And all of them seems fine. (Sheet1)

Please Login or Register  to view this content.

2nd approach is same as above only it use Randbetween function.
And it look like for Cells(random, random) case again it's not random and pattern is started to create same accumulation points on diagonal.
Check sheet2.

Please Login or Register  to view this content.

@shg
your z2 produces results consistent with z1 for me in 2010?

Can you check rand_test.xlsm‎ from post #7?

Download file, delete results in sheet2 (sheet1 is irrelevant since it's working fine) and then run Z4.

See how it's distributed now and let us know how it work in 2010

For me as well, JP. That's very curious.

I wonder if it is related to the changed RAND algorithm in 2010?

I didn't know the RAND algorithm had changed since the major re-do for 2003. Got a link?

What seems VERY strange is that RANDBETWEEN behaves differently in formulas versus VBA. I feel like a dope slap is coming soon.

RAND seems to work fine. RANDBETWEEN has problem within VBA and, as I managed to test in those few days, only in those above example when it's used as Cells(x,y).
Other testing seemed to work fine.

ms article here: http://office.microsoft.com/en-us/ex...010355760.aspx

nothing in the way of detail though

Interesting -- some variant of Mersenne Twister with a super-long period. See http://homepages.ulb.ac.be/~gmelard/...ard_csda23.pdf

I think I played that at a party in college once

In Utah? I'm shocked.

:-)

I did not go to college here