+ Reply to Thread
Results 1 to 18 of 18

Wrong coding or RANDBETWEEN is wrong?

  1. #1
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    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?
    Attached Files Attached Files

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Wrong coding or RANDBETWEEN is wrong?

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

    Rnd.png

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Wrong coding or RANDBETWEEN is wrong?

    I'd prefer:

    Please Login or Register  to view this content.
    Last edited by snb; 07-28-2013 at 04:43 PM.



  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Wrong coding or RANDBETWEEN is wrong?

    @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.
    Last edited by snb; 07-29-2013 at 03:52 PM.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Wrong coding or RANDBETWEEN is wrong?

    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.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Wrong coding or RANDBETWEEN is wrong?

    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.

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Wrong coding or RANDBETWEEN is wrong?

    Quote Originally Posted by shg View Post
    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.
    Attached Files Attached Files

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Wrong coding or RANDBETWEEN is wrong?

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

    if at first you don't succeed try doing it the way your wife told you to

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Wrong coding or RANDBETWEEN is wrong?

    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

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Wrong coding or RANDBETWEEN is wrong?

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

  11. #11
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Wrong coding or RANDBETWEEN is wrong?

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

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Wrong coding or RANDBETWEEN is wrong?

    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.

  13. #13
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Wrong coding or RANDBETWEEN is wrong?

    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.

  14. #14
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Wrong coding or RANDBETWEEN is wrong?

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

    nothing in the way of detail though
    Last edited by JosephP; 07-31-2013 at 09:18 AM.

  15. #15
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Wrong coding or RANDBETWEEN is wrong?

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

  16. #16
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Wrong coding or RANDBETWEEN is wrong?

    I think I played that at a party in college once

  17. #17
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Wrong coding or RANDBETWEEN is wrong?

    In Utah? I'm shocked.

  18. #18
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Wrong coding or RANDBETWEEN is wrong?

    :-)

    I did not go to college here

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. What am I doing wrong??
    By nimblejack in forum Excel General
    Replies: 2
    Last Post: 01-10-2010, 12:14 AM
  2. What Am I doing wrong here?
    By Aurbo99 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2007, 09:32 PM
  3. What am I doing wrong....!!!
    By squoggy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-02-2005, 01:05 PM
  4. [SOLVED] What am I doing wrong?
    By Jeff in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 03-05-2005, 11:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1