+ Reply to Thread
Results 1 to 11 of 11

Generating Random numbers that will sum to 1 specific value?

  1. #1
    Registered User
    Join Date
    07-15-2010
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    2

    Question Generating Random numbers that will sum to 1 specific value?

    Hey guys I am new to the forum and excel Macros, so go easy on me! Here is my problem:

    I need to generate 10 random numbers, lets call that x that if summed/added will equal a set value of 100,000 lets call that y. I also need the generated random numbers to be distributed between 5,000 to 20,000 lets say the lower value of 5,000 is min and the upper value is max. I them need these values placed in a set number of cells, for example A1 to A10 . What would a macro that did something like this look like. Any help would be very appreciated!

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Generating Random numbers that will sum to 1 specific value?

    A set number of numbers, with a set minimum, set maximum and set total can not be random.

    You have to lose at least one of these parameters in order to get random numbers.
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  3. #3
    Registered User
    Join Date
    07-15-2010
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Generating Random numbers that will sum to 1 specific value?

    Quote Originally Posted by Cheeky Charlie View Post
    A set number of numbers, with a set minimum, set maximum and set total can not be random.

    You have to lose at least one of these parameters in order to get random numbers.
    Perhaps Random was the wrong choice of terms... Lets say pseudo random or constrained number within a possible set of numbers. Essentially it's just a probability outcome question where the numbers are constrained by reasonable possibilities as to create relatively uniformed output range that do not exceed a sum. Thanks for the quick input though... Given this new term definition do you have any ideas how I could solve this problem??

  4. #4
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Generating Random numbers that will sum to 1 specific value?

    Yes, it's easy enough to create pseudo-randoms within certain bounds, but it's very difficult if there isn't 'play' in at least one of the bounds, for example, a set of five random numbers from min 5 to max 10 which must total 50 leaves very few options.

    This is not to be stubborn, but you've really got to let one of the bounds go to have a chance.

    In terms of maths, depending on what you need to keep, solutions can be very simple, e.g.
    10 random numbers (0 - 1) in A1:A10 then B1=A1*20000/sum(A$1:A$10) - each number is random, but the are normalised up such that the sum of the random numbers = 20000, but haven't fixed start or end points.

    So, what could we lose?

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

    Re: Generating Random numbers that will sum to 1 specific value?

    See if this works for you:
    Please Login or Register  to view this content.
    Copy the code to a Code module (see below), then select A1:A10 and use the formula,

    =TRANSPOSE(RandTot(100000, 5000, 20000))

    The formula MUST be confirmed with Ctrl+Shift+Enter rather than just Enter.

    Adding a Macro to a Code Module
    1. Copy the code from the post
    2. Press Alt+F11 to open the Visual Basic Editor (VBE)
    3. From the menu bar in the VBE window, do Insert > Module
    4. Paste the code in the window that opens
    5. Press Alt+Q to close the VBE and return to Excel
    Last edited by shg; 07-15-2010 at 01:27 PM. Reason: Modest improvements
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    02-06-2012
    Location
    Tallahssee, Florida
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Generating Random numbers that will sum to 1 specific value?

    @SHG
    how can i change that code so that i can populate 100 cells... i just dont see where it in the macro code there
    thanks so much
    Chuck

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

    Re: Generating Random numbers that will sum to 1 specific value?

    Populate 100 cells with what?

  8. #8
    Registered User
    Join Date
    10-07-2013
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Generating Random numbers that will sum to 1 specific value?

    Quote Originally Posted by shg View Post
    See if this works for you:
    Please Login or Register  to view this content.
    Copy the code to a Code module (see below), then select A1:A10 and use the formula,

    =TRANSPOSE(RandTot(100000, 5000, 20000))

    The formula MUST be confirmed with Ctrl+Shift+Enter rather than just Enter.

    Adding a Macro to a Code Module
    1. Copy the code from the post
    2. Press Alt+F11 to open the Visual Basic Editor (VBE)
    3. From the menu bar in the VBE window, do Insert > Module
    4. Paste the code in the window that opens
    5. Press Alt+Q to close the VBE and return to Excel
    Hello Sir,

    I tried above code with below formula

    =TRANSPOSE(RANDTOT(750,1,90))

    it is giving me Time-Out. Is there a way out for this code to work for my criteria ? My requirement is sum to be 750 and numbers should be between 1 to 90.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,707

    Re: Generating Random numbers that will sum to 1 specific value?

    Hi.

    It's a forum rule that you start your own thread, rather than try to "hijack" someone else's thread. This helps prevent massive confusion arising -which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution). Also, preferably attach an Excel sheet. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!
    Glenn



  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,707

    Re: Generating Random numbers that will sum to 1 specific value?

    To attach a file:
    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  11. #11
    Registered User
    Join Date
    11-19-2014
    Location
    Mumbai, Maharashtra, INDIA
    MS-Off Ver
    2003
    Posts
    52

    Re: Generating Random numbers that will sum to 1 specific value?

    @Shg;

    What is the logic to Time-Out? If I am using =TRANSPOSE(RandTot(50,5,20,1)) it allows upto 6 rows and beyond it displays Time-Out.

+ 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