The Notebook Review forums were hosted by TechTarget, who shut down them down on January 31, 2022. This static read-only archive was pulled by NBR forum users between January 20 and January 31, 2022, in an effort to make sure that the valuable technical information that had been posted on the forums is preserved. For current discussions, many NBR forum users moved over to NotebookTalk.net after the shutdown.
Problems? See this thread at archive.org.

    Generating Random Number in Excel

    Discussion in 'Windows OS and Software' started by Lawrence, Nov 17, 2008.

  1. Lawrence

    Lawrence Notebook Evangelist

    Reputations:
    255
    Messages:
    492
    Likes Received:
    0
    Trophy Points:
    30
    There are many of ways to generate a random number by creating a macro written in VBA or writing your own function by using Rand() formula that may differ in many ways.

    I made a VBA program in Excel 2007:

    Generating a Value Between two Numbers
    Int((Upperbound - Lowerbound + 1) * Rnd + Lowerbound)

    e.g. for 1-10, Integer of ((10-1+1) * Rnd + 1)

    That's why most programmers only use Int(10 * Rnd + 1)

    Where Rnd is almost the same as Rand() is Excel


    If you can't understand and you want to know the step by step procedure,
    more explanation and it's logic: Click Here

    Download the .xlsm file here (macro enabled document)

    Here's the old office 2000-2003 format. .xls file, download here
    I'm sure, it will work.

    If you know more ways on How to generate numbers in random without repetition, Please Share it to us!!! Thanks in advance! :)
     
    Lawrence, Nov 17, 2008
    #1
  2. Meetloaf13

    Meetloaf13 fear the MONKEY!!!

    Reputations:
    547
    Messages:
    1,717
    Likes Received:
    42
    Trophy Points:
    66
    .xlsm doens't work.

    I enable macros on the file and the number box enlarges and hides the button. Hence, can't run the macro.
     
    Meetloaf13, Nov 17, 2008
    #2
  3. Lawrence

    Lawrence Notebook Evangelist

    Reputations:
    255
    Messages:
    492
    Likes Received:
    0
    Trophy Points:
    30
    you can create it using the .xls old format... just follow the procedure and it's easy. sorry it's too long and difficult to explain.

    hope this helps...
     
    Lawrence, Nov 17, 2008
    #3
  4. Pirx

    Pirx Notebook Virtuoso

    Reputations:
    3,001
    Messages:
    3,005
    Likes Received:
    416
    Trophy Points:
    151
    I have trouble seeing what the purpose of this might be. Why not just use the built-in rand function? More fundamentally, by eliminating "duplicates", your algorithm does generate a list of numbers that re not true random numbers. I don't want to go into the mathematics of this, but I will note that generating true random numbers on a deterministic machine is not strictly possible anyway, but that generating a good approximation to such numbers is not at all trivial...
     
    Pirx, Nov 17, 2008
    #4
  5. Lawrence

    Lawrence Notebook Evangelist

    Reputations:
    255
    Messages:
    492
    Likes Received:
    0
    Trophy Points:
    30
    Hello Pirx, Can you post some examples? No problem, I'm always open for discussion. Thanks in advance.
     
    Lawrence, Nov 17, 2008
    #5
  6. Pirx

    Pirx Notebook Virtuoso

    Reputations:
    3,001
    Messages:
    3,005
    Likes Received:
    416
    Trophy Points:
    151

    You start by going to Wikipedia for an introduction into the topic. The section on pseudo-random numbers is pertinent.
     
    Pirx, Nov 17, 2008
    #6
  7. Lawrence

    Lawrence Notebook Evangelist

    Reputations:
    255
    Messages:
    492
    Likes Received:
    0
    Trophy Points:
    30
    Here's the old office 2000-2003 format. .xls file, download here
    I'm sure, it will work.

    Anyway, I'm always open for discussion. It will help many people.
    Please post more Examples.
     
    Lawrence, Nov 17, 2008
    #7
  8. Meetloaf13

    Meetloaf13 fear the MONKEY!!!

    Reputations:
    547
    Messages:
    1,717
    Likes Received:
    42
    Trophy Points:
    66
    I got it, just in case anyone else has the same problem, make the width of column A bigger and the button will be moved over.
     
    Meetloaf13, Nov 17, 2008
    #8
  9. Lawrence

    Lawrence Notebook Evangelist

    Reputations:
    255
    Messages:
    492
    Likes Received:
    0
    Trophy Points:
    30
    Hello Pirx,

    Came From Microsoft Excel and Visual Basic 6.0 Help Files:
    You can also modify the Code by adding Randomize Statement to initialize the seed.

    Maybe you can post a sample code of your own?
     
    Lawrence, Nov 17, 2008
    #9