Help on Random Numbers

S

sara

Hi.
It would help to know how I could
Fill up random numbers in say Cell A1 to E1
but the total of the random numbers should add up to say number 100
please help.
 
J

JulieD

Hi

one option is in cells
A1:D1 use the formula
=RANDBETWEEN(1,20)
and in E1 use the formula
=100-SUM(A1:D1)

Cheers
JulieD
 
S

sara

Hi JuliD
tried that. works fine. but sometimes cell E1 displays number that is too
large
from the rest of the cells. This is because the total is subtracted from 100.
I would prefer "evenly" distributed numbers. Are there any formulars to do
that ?
 
J

JulieD

Hi Sara

not aware of any way of achieving this - even using code it would be
difficult as you'ld have to "loop" it until you found an "evenly
distributed" series of numbers and then one would question the concept of
"random", especially as you're only taking about 5 numbers!

sorry

Cheers
JulieD
 
S

Sukhjeet

Hi Sara
You could do this:
In A1 to E1, type formula =rand().
In A2 to E2 type =A1*100/SUM($A$1:$E$1) (where 100 is the sum of all the
random numbers you require)
This will give you the numbers whose some equals 100.
 
B

Bill Sharpe

If the numbers must add to 100 the last number is not really random
because there's a constraint involved. Of course the RAND function
really gives you pseudo-random numbers anyway, good enough for most
practical purposes.

You can avoid getting a large number as the fifth number by changing the
upper limit of RANDBETWEEN to a number higher than 20. Using 20 as the
upper limit guarantees that the fifth number will always be 20 or
greater. Don't go too high or the fifth number will be too small
compared to the others. You'll have to experiment a bit.

Bill

Hi JuliD
tried that. works fine. but sometimes cell E1 displays number that is
too
large
from the rest of the cells. This is because the total is subtracted from
100.
I would prefer "evenly" distributed numbers. Are there any formulars to
do
that ?
 
S

Sukhjeet

Hi Bill
The method I proposed, of generating five random numbers, multiplying each
with the total required (100 in this case) and then dividing with the sum of
those five random numbers will result in a better randomisation than
generating 4 random numbers (between 1 and 20) and subtracting their sum from
100.
Ciao
Sukhjeet
 
S

sara

Hi Guys,
Thanks for the brilliant suggestions. I prefer Sukhjeet's method as JulieD
suggestion; at random produces negative number on the last cell and also
random numbers appear procedural in the first 4 cells but not in the last
one(i.e if at random A1 to D1 produces nos 1,4,6,6.then the last one produces
100-1-4-6-6.this is a huge different) I am just afraid if this random row
might go unnoticed. Thanks guys. And by the way I know F9 randoms the entire
spreadsheet but how do I random just one row out of the hundreds?
 
Top