+ Reply to Thread
Results 1 to 8 of 8

Return random number based on probability

  1. #1
    Registered User
    Join Date
    11-09-2010
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    32

    Return random number based on probability

    Hi,

    Thanks in advance for any help. I have a reasonably complex VBA simulation that I am trying to modify. It is supposed to be modelling movements from one region to another.

    I have a variable, "Region", which can be anything from 1-5. I need a function that will change this variable to 1-5 based on probabilities that I pass it.

    e.g. If Region is currently 1, there is a 20% chance it will become a 2, and 10% chance it will become a 3, a 50% chance a 4 etc.

    I know you can do similar things to this in excel using array formulas but was wondering if there was a way to do it in VBA because the regions / probabilities will be changing so I need to pass them to the function using an array.

    Hope this makes sense,

    Cheers
    Gareth
    Last edited by gazza365; 02-28-2011 at 08:47 PM.

  2. #2
    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: Return random number based on probability

    What's are examples of how you want to pass data to this function?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-09-2010
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Return random number based on probability

    Hi,

    For example I can read in the following as an array:

    Region 1 Region 2 Region 3 Region 4 Region 5
    Region 1 0.000 0.200 0.100 0.100 0.600
    Region 2 0.100 0.000 0.600 0.100 0.100
    Region 3 0.100 0.600 0.000 0.200 0.100
    Region 4 0.100 0.600 0.200 0.000 0.100
    Region 5 0.100 0.600 0.200 0.100 0.000

    And then I have a variable called region which will be a number from 1 to 5. I want to somehow randomly change it to a new number 1-5 based on these probabilities.

    I know that in excel I could use a formula such as:
    =INDEX($C$2:$G$2, MATCH(RAND(), C3:G3) )

    But I'm not quite sure how to do this in VBA.

    Thanks
    Gareth

  4. #4
    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: Return random number based on probability

    I don't know how to interpret your table; I see that the probabilities total 1 in each row but don't whats next -- return a number 1-5 in each row based on those probabilities?

    I know that in excel I could use a formula such as:

    =INDEX($C$2:$G$2, MATCH(RAND(), C3:G3) )
    Not really -- you can do a lookup sort of like that against the cumulative distribution functon, not the mass distribution function, and you'd need a leading zero in each row in the table.

  5. #5
    Registered User
    Join Date
    11-09-2010
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Return random number based on probability

    Hi,

    Sorry, I've uploaded an example workbook to try and explain myself better.

    The code in the VBA is a shortened version of what I have. I've added comments so hopefully you can see what I need.

    I have a feeling what I am trying to do is quite simple but I am very new to VBA so am having trouble.

    Thanks for you help,
    Gareth
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Return random number based on probability

    Please Login or Register  to view this content.

  7. #7
    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: Return random number based on probability

    See attached.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-09-2010
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Return random number based on probability

    Great, thanks very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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