Random numbers to make a known amount

S

Smeeg

Hi there,

In cell A11 I have the value 100

Is there a formula I can put in A1:10 which has random numbers in it
(to 2 decimal places) which totals 100?

Thanks,

AJ
 
P

prabhuraaman

There is no direct fomula
But you can do it indirectly:

Let a1 be 100
in cell a2,type the formula rand()*10
in cell a3,type rand()*100-a2
in cell a4,type rand()*100-sum(a2:a3)
..
..
..
in cell a9,rand()*100-sum(a2:a9)
in cell a10,100-sum(a2:a9)

Hope you got the idea.The only disadvantage is there may be a negative
value in cell a10.
To avoid this and to get better results, use the SOLVER option(found in
add-ins menu)

For more,visit and join
http://groups.google.co.in/group/Answers-for-everything
 
S

Smeeg

Hi there,

I've put these values in cells A1:A10 (obviously with the = in front of
each formula)

100.00
RAND()*10
RAND()*100-A2
RAND()*100-SUM(A$2:A3)
RAND()*100-SUM(A$2:A4)
RAND()*100-SUM(A$2:A5)
RAND()*100-SUM(A$2:A6)
RAND()*100-SUM(A$2:A7)
RAND()*100-SUM(A$2:A8)
RAND()*100-SUM(A$2:A9)

But the total of A2:A10 never equals 100

Am I doing somthing wrong?

Thanks,
 
S

Smeeg

a-ha! I see what I'm doing wrong - I used the wrong formula in the final
cell...

basically, this is what I should do:

=100.00
=RAND()*10
=RAND()*100-A2
=RAND()*100-SUM(A$2:A3)
=RAND()*100-SUM(A$2:A4)
=RAND()*100-SUM(A$2:A5)
=RAND()*100-SUM(A$2:A6)
=RAND()*100-SUM(A$2:A7)
=RAND()*100-SUM(A$2:A8)
=100-SUM(A$2:A9)

Thanks for your help.
 
J

JE McGimpsey

First, a maximum of 9 of the numbers can be random. The 10th number (at
least) will necessarily be determined by 100 minus the sum of the others.

Second, if you're only including non-negative numbers, this will work,
though you're likely to get lots of zeros:

A1: =INT(RAND()*$A$11)
A2: =INT(RAND()*($A$11-SUM(A$1:A1)+1))

copy A2 down to A9

A10: =$A$11-SUM($A$1:$A$9)

If you allow negative numbers, the formula can get a bit more
complicated. If you want a more uniform distribution, you'll have to
specify how non-random you want it.
 
S

Smeeg

thanks for the responses.

Both of the suggestions above seem to work equally well.

Is it possible to create a random figure from the formulas that always
result in a positive figure?

and a random figure that doesn't vary too much?

i.e. I want the total of the random numbers to equal 100, but I don't
want the random numbers to be 'too' random i.e. maybe random between
5-15 or something .....at the moment, the first random formula produces
a large number and the final random number is a small number (obviously
thats because each successive random formula is reducing the maximum
random number by the previous results).

I wouldn't worry about trying to solve the 'uniform' result - just a
positive figure result will be fine (unless you want to try and create
a positive AND uniform result, then go ahead :D)

my spreadsheet is actually working on a total value of 250,000 from a
string of 25 random numbers :D (rather than the example here of 100
being the total and 10 random numbers).
 
H

Herbert Seidenberg

For 10 uniform, positive integers in the range from 5 to 15
and a sum of 100, fill a 10 cell range, named set1, with
=randbetween(5,15)
Into a cell named sum1, enter
=sum(set1)
Tools > Goal Seek
Set cell: sum1
To value: 100
By changing cell: any unrelated cell

This will work most of the time if the average of the integers
(10 in this case) is close to the sum divided by the number
of integers (100/10)
 
B

bplumhoff

Hello,

I suggest to take my UDF RandSum1() and multiply by 100:

http://www.sulprobil.com/html/randsum1.html

Choose one of the proposed distributions...

You might want to adjust the resulting numbers by rounding to two
digits and finally adjust of them (maybe the highest so that all are >
0) to get 100.0.

HTH,
Bernd
 
Top