Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique RANDOM NUMBERS within specified range | Excel Worksheet Functions | |||
can you assign random numbers to many different lines of row? | Excel Worksheet Functions | |||
random numbers | Excel Worksheet Functions | |||
Random Numbers | Excel Worksheet Functions | |||
I wish to change the last digit in a list of random numbers. To a. | Excel Worksheet Functions |