RANDOM NUMBERS TO SUM 100%

Question

RANDOM NUMBERS TO SUM 100%

Anonymous

Any method to have the =RAND() function generating random fractions in four different cells which would add up to exact 100%? or =RANDBETWEEN() function to provide an array of 10 different numbers summing upto exact 100?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

0 additional answers

Answer 1

You probably need to do this in two columns. The first column simply gathers four random numbers, each between 0 and 1. Then second column "normalizes" the result by dividing all four of the random numbers by the sum of the four, such as:

=RAND()	=A1/SUM($A$1:$A$4)
=RAND()	=A2/SUM($A$1:$A$4)
=RAND()	=A3/SUM($A$1:$A$4)
=RAND()	=A4/SUM($A$1:$A$4)
=SUM(A1:A4)	=SUM(B1:B4)

Which yields, for example:

0.97	0.54
0.14	0.08
0.61	0.34
0.07	0.04
1.79	1.00

Note that the second column will always sum to 1.0. You can expand this to 10 different random numbers easily. I don't think you need to use RANDBETWEEN().

HTH,

Eric

Answer 2

Hi,

Problem 1.

4 different numbers that add up to 100.

In A2 enter =RAND() and drag down to A5

In A1 enter =SUM(A2:A5)

Enter this in B1 and drag down to B4

=(A2/$A$1)*100

The four numbers produced will now add up to 100. You can hide column A if you want or use a column somewhere out of the way.

EDIT. If you want the numbers to addup to 1 instead of 100 use

=A2/$A$1

Problem 2. Please define what you mean because you say RANDBETWEEN and this can't be between 1 and 100 because the first number could be 100

Share via

RANDOM NUMBERS TO SUM 100%

0 additional answers