Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Smeeg
 
Posts: n/a
Default Random numbers to make a known amount


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


--
Smeeg
------------------------------------------------------------------------
Smeeg's Profile: http://www.excelforum.com/member.php...o&userid=29099
View this thread: http://www.excelforum.com/showthread...hreadid=518655

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Random numbers to make a known amount

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Smeeg
 
Posts: n/a
Default Random numbers to make a known amount


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,


--
Smeeg
------------------------------------------------------------------------
Smeeg's Profile: http://www.excelforum.com/member.php...o&userid=29099
View this thread: http://www.excelforum.com/showthread...hreadid=518655

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Smeeg
 
Posts: n/a
Default Random numbers to make a known amount


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.


--
Smeeg
------------------------------------------------------------------------
Smeeg's Profile: http://www.excelforum.com/member.php...o&userid=29099
View this thread: http://www.excelforum.com/showthread...hreadid=518655

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JE McGimpsey
 
Posts: n/a
Default Random numbers to make a known amount

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.


In article ,
Smeeg wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Smeeg
 
Posts: n/a
Default Random numbers to make a known amount


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).


--
Smeeg
------------------------------------------------------------------------
Smeeg's Profile: http://www.excelforum.com/member.php...o&userid=29099
View this thread: http://www.excelforum.com/showthread...hreadid=518655

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Smeeg
 
Posts: n/a
Default Random numbers to make a known amount


oops - double post -

<<deleted


--
Smeeg
------------------------------------------------------------------------
Smeeg's Profile: http://www.excelforum.com/member.php...o&userid=29099
View this thread: http://www.excelforum.com/showthread...hreadid=518655

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
 
Posts: n/a
Default Random numbers to make a known amount

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)

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Random numbers to make a known amount

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unique RANDOM NUMBERS within specified range Matt D Francis Excel Worksheet Functions 5 July 15th 08 08:04 PM
can you assign random numbers to many different lines of row? random number generator Excel Worksheet Functions 3 February 28th 06 07:35 PM
random numbers John Murf Excel Worksheet Functions 1 February 22nd 06 01:39 AM
Random Numbers anuterrnd Excel Worksheet Functions 1 July 22nd 05 05:32 AM
I wish to change the last digit in a list of random numbers. To a. kingie Excel Worksheet Functions 5 February 28th 05 09:17 PM


All times are GMT +1. The time now is 10:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"