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

1. ## 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!  Register To Reply

2. ## 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.  Register To Reply

3. ## Re: Generating Random numbers that will sum to 1 specific value? Originally Posted by Cheeky Charlie 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??  Register To Reply

4. ## 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?  Register To Reply

5. ## 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  Register To Reply

6. ## 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  Register To Reply

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

Populate 100 cells with what?  Register To Reply

8. ## Re: Generating Random numbers that will sum to 1 specific value? Originally Posted by shg 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
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.  Register To Reply

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

Hi.  Register To Reply

10. ## 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.  Register To Reply

11. ## 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.  Register To Reply