+ Reply to Thread
Results 1 to 11 of 11

Generating Random Number with Probability

  1. #1
    Registered User
    Join Date
    02-06-2016
    Location
    singapore
    MS-Off Ver
    2010
    Posts
    2

    Generating Random Number with Probability

    Hi, sorry i am still an excel newbie here. and i have a question.
    i would like to generate random number from column D with effect on the probability from column A
    but however, i keep getting errors such as #NA and the numbers generated does not make sense, as my probability say 30% is the highest among them, and yet it dont really appear much


    test.jpg

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Generating Random Number with Probability

    Hi ixander and welcome to the forum,

    I think you are looking for the Choose() function with a bunch of RandBetween() within the choices. Something like:

    =CHOOSE(RANDBETWEEN(1,6),RANDBETWEEN(1,5),RANDBETWEEN(6,15),RANDBETWEEN(16,35),RANDBETWEEN(36,65),RANDBETWEEN(66,85),RANDBETWEEN(86,100))
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    02-06-2016
    Location
    singapore
    MS-Off Ver
    2010
    Posts
    2

    Re: Generating Random Number with Probability

    thank you MarvinP for the fast reply, but what about the probability? does that formula take into account of the probabilities?

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Generating Random Number with Probability

    Hi,
    I think it does. Pull the formula down a few hundred rows and see.

  5. #5
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Generating Random Number with Probability

    Quote Originally Posted by ixander View Post
    i would like to generate random number from column D with effect on the probability from column A
    Attachment 445043
    See the "Correct Distrib" worksheet in the attached Excel file.

    The formula that I use in A11:A10010 is (see the comment for A11):
    Please Login or Register  to view this content.
    C2:C7 are the cumulative probabilities, starting with 0%. The values in D3:E3 through D8:E8 are the lower and upper values of each range of numbers.

    The distribution of the values in A11:A10010 is consistent with the required probabilities in A3:A8, to wit:

    C D E
    10
    Bin
    Freq
    Bin Prob
    11
    5
    496 4.96%
    12
    15 960 9.60%
    13
    35 2032 20.32%
    14
    65 2999 29.99%
    15
    85 1988 19.88%
    16
    100 1525 15.25%
    17
    SUM 10000

    Quote Originally Posted by MarvinP View Post
    Please Login or Register  to view this content.
    That has the wrong distribution. The probability for each range of numbers is uniformly 1/6, about 16.67%. See the "MarvinP" worksheet in the attached Excel file.

    The distribution of the values in A2:A10001 is (see the comment in A2):


    C
    D
    E
    2
    Bin
    Freq Bin Prob
    3
    5 1694 16.94%
    4
    15 1560
    15.60%
    5
    35 1738 17.38%
    6
    65 1652 16.52%
    7
    85 1692 16.92%
    8
    100 1664 16.64%
    9
    SUM 10000
    10
    1/6 16.67%
    Attached Files Attached Files
    Last edited by joeu2004; 02-06-2016 at 05:05 AM. Reason: cosmetic

  6. #6
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Generating Random Number with Probability

    Quote Originally Posted by joeu2004 View Post
    The formula that I use in A11:A10010 is (see the comment for A11):
    Please Login or Register  to view this content.
    Much ado about nothing, perhaps. The distribution that ixander describes in the initial posting is simply one that comes from a uniform distribution between 1 and 100 inclusive.

    For example, we expect the numbers 1 to 5 about 5% of the time, the numbers 6 to 15 about 10%, etc.

    See the "Simple Distrib" worksheet in the attachment.

    The formula in A2:A10001 is simply =RANDBETWEEN(1,100). (See the comment in A2.) It produced the following distribution:


    C
    D
    E
    1
    Bin
    Freq Bin Prob
    2
    5 490 4.90%
    3
    15 987 9.87%
    4
    35 2028 20.28%
    5
    65 3034 30.34%
    6
    85 1978 19.78%
    7
    100 1483 14.83%
    8
    SUM
    10000


    Ixander needs the more complicated CHOOSE(RAND,RANDBETWEEN,...) formula if the distribution in the initial posting is only an example, and ixander requires the flexibility for each group to have a different probability than it would with a uniform distribution.
    Attached Files Attached Files
    Last edited by joeu2004; 02-06-2016 at 05:57 AM. Reason: added attachment

  7. #7
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Generating Random Number with Probability

    Quote Originally Posted by ixander View Post
    i would like to generate random number from column D with effect on the probability from column A
    Quote Originally Posted by joeu2004 View Post
    Much ado about nothing, perhaps.
    Indeed! I awoke this morning realizing that ixander probably misunderstood the assignment. Refer to the table below, which summarizes and embellishes ixander's JPEG and my attached Excel file.

    Ixander probably needs to generate the demands in column B (B3:B8) according to the probability distribution in column A (A3:A8). Notice that the ultimate objective is to simulate demand starting in C11.

    Someone might have told ixander to accomplish that by using RANDBETWEEN to generate integers in corresponding ranges (D1:D8, which are not needed for my purposes). That is a misdirection. But if the assignment requires that, I will explain how to change the paradigm below to that end.

    The table below shows one way to simulate demand in rows 11 through 10011 (excerpted).


    A
    B
    C
    D
    E
    F
    G
    1
    Prob
    Demand Cuml Prob Rnd Intvl



    2


    0.00%



    3
    5.00% 0 5.00%
    1 to 5



    4
    10.00% 1 15.00% 6 to 15


    5
    20.00% 2 35.00% 16 to 35


    6
    30.00% 3 65.00% 36 to 65


    7
    20.00% 4 85.00% 66 to 85


    8
    15.00% 5 100.00% 86 to 100


    9







    10
    Day
    Rnd# Sim Demand
    Demand Freq Prob
    11
    1 0.9242114 5
    0 479 4.79%
    12
    2 0.8095377
    4
    1 1021 10.21%
    13
    3 0.2860609 2
    2 1958 19.58%
    14
    4 0.2308978 2
    3 3078 30.78%
    15
    5 0.8072394 4
    4 1969 19.69%
    16
    6 0.8546513 5
    5 1495 14.95%
    17
    7 0.8199888 4
    TOTAL
    10000
    18
    8 0.0187098 0



    19
    9 0.3324532 2




    etc
    etc
    etc




    Please Login or Register  to view this content.
    Formulas displayed with curly brackets {...} are array-entered, to wit: type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

    The frequency distribution table in E11:G16 demonstrates the probability distribution for the simulation, which is similar to A3:A8.

    If the assignment requires that the random numbers starting in B11 be integers 1 through 100, make the following changes with appropriate changes to cell formats and column titles (ERRATA in red):
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by joeu2004; 02-06-2016 at 02:59 PM. Reason: cosmetic; ERRATA

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Generating Random Number with Probability

    Hi Joeu,

    Many of these questions are vague. I was thinking if he tried a Choose() function and then tested it using a frequency, s/he would discover if what I had given him was correct or not. It looks like you've gone to much deeper lengths than me. I wonder now what the real question was.

  9. #9
    Registered User
    Join Date
    09-07-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Generating Random Number with Probability

    Hi MarvinP,

    I have read this post and tried to adopt your suggestions - and with some success. I now have two columns with auto generated numbers. Column 1 ranging from 1-150 and column 2 ranging from 5-450. I have used the =Choose(Match(Rand.....Randbetween function as described above. My challenge is that I need the rows of random numbers to match the interval they are generated from. Sounds obscure! I know.
    PrintScreen.jpg
    As seen from the image - the variation between column A and G is to much compared to the interval defined in rows 3-8. Do you have any suggestions on how to solve this issue?

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Generating Random Number with Probability

    Hi FiberFlux,
    I see you are pretty new to this forum. We like you to start your own thread instead of asking a new question at the bottom of someone else's question. Could you start your own new thread please?

    In your post above you write:
    My challenge is that I need the rows of random numbers to match the interval they are generated from.
    I wish I knew what that meant. If you could give a few examples of what numbers are accepted and why, along with what answers are not allowed and why, it might help us understand the problem and get you the answer that you need.

  11. #11
    Registered User
    Join Date
    09-07-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Generating Random Number with Probability

    Hi MarvinP.

    Sorry for my lack of forum rules. I made a new post http://www.excelforum.com/showthread...61#post4440161

+ 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. Generating random outcomes based on probability
    By JP2008 in forum Excel General
    Replies: 9
    Last Post: 12-07-2015, 04:48 AM
  2. [SOLVED] Generate random number from probability
    By shinzai in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-15-2013, 11:53 AM
  3. Replies: 0
    Last Post: 04-20-2013, 07:17 PM
  4. Need Random Number Generator but with Weighted Probability for Certain Numbers...
    By michaeljoeyeager in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-13-2012, 03:52 PM
  5. Need Help with a Random Number Generator with Weighted Probability for Certain Numbers
    By michaeljoeyeager in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2012, 02:48 PM
  6. Return random number based on probability
    By gazza365 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-28-2011, 08:46 PM
  7. Random number generating
    By dagindi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-14-2010, 03:39 PM
  8. Generating a Random Number of Random Numbers
    By Garrus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2010, 09:39 AM

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