write somewhere at side (lets say in J1)
copy to next 4 cells right (J1:N1)Code:=RAND()
in A2:
and copy right to A2:E2Code:=J1*100/SUM($J1:$N1)
Enjoy !
Hi All, I have Columns as
A B C D E
1 Age(18-24) Age(25-34) Age(35-44) Age(45-54) Age(55-Above)
2
I want random number generated in all the columnin 2^{nd} row i.e. A,B,C,D,E such that whose sum(A+B+C+D+E) will be 100 thanks in advance.
Last edited by Ashwini Lambture; Jul 24th, 2014 at 08:39 AM.
write somewhere at side (lets say in J1)
copy to next 4 cells right (J1:N1)Code:=RAND()
in A2:
and copy right to A2:E2Code:=J1*100/SUM($J1:$N1)
Enjoy !
You may look into using the Solver Add In. It will set the values for you. However, if you are trying to keep the numbers within the number ranges (Age) in your headers this is not possible:
Excel 2012
A B C D E F 1 Lower Limit of Ranges 2 18-24 25-34 35-44 45-54 55-Above SUM 3 18 25 35 45 55 178 Sheet1
Worksheet Formulas
Cell Formula F3 =SUM(A3:E3)
If you would like to generate Random numbers between these ranges you should use the function:
ie:Code:=RANDBETWEEN(BOTTOM, TOP)
Code:=RANDBETWEEN(18, 24)
Last edited by mrmmickle1; Jul 24th, 2014 at 11:12 AM.
Matt Mickle
Microsoft MVP - Excel
Using Excel & Access 2010, 2013 & 2016 | Windows 7 | 64 Bit
Post a screen shot with the MrExcel HTML Maker Add-In or the Excel Jeanie Add-In
"Be careful what you wish for". A random number generator might result in 0, 0, 0, 0, 100 for the respective age groups. Although that extreme is unlikely, the point is: one or two groups might have 0 or 1. So you might want to put some additional constraints on the problem.
In any case....
That is a great answer. One caveat: SUM(A2:E2) might be slightly less than or greater than 100 when each result is rounded to an integer. The following embellishment avoids that:
A2: =ROUND(100*J1/SUM($J$1:$N$1),0)
B2: =ROUND(100*SUM($J$1:J2)/SUM($J$1:$N$1)-SUM($A$2:A2),0)
Copy B2 into C2:E2.
Hi All,Thank you very much for your reponse. @Kaper Random numbers are generated in A,B,C,D,E colmns in 2nd row whose sum is equivalent to 100.But I want random numbers to be generated in next 100 rows in the same columns.I have generated also but for this I copied RAND() function from J1to J100,K1 to K100,L1 to L100,M1 to M100,N1 to N100.Is it the right method to do it. I also want to ask you @ALL ,is there any other method to do it?.......................................I m waiting of your response.
Hi All,Thank you very much for your reponse. @Kaper Random numbers are generated in A,B,C,D,E colmns in 2nd row whose sum is equivalent to 100.But I want random numbers to be generated in next 100 rows in the same columns.I have generated also but for this I copied RAND() function from J1to J100,K1 to K100,L1 to L100,M1 to M100,N1 to N100.Is it the right method to do it. I also want to ask you @ALL ,is there any other method to do it?.......................................I m waiting of your response.
@Ashwini - Yes, you can copy the formulas down as may times as needed.
@joeu2004 - As I can see, Ashwini has not mentioned requirement for integer values.
If one wants a bit more elegant opiton - an UDF could be used - see attachment at the bottom of the page (text is in Polish, but excel file is universal):
http://klubexcela.pl/home/kategorie-...5-randsum.html
The UDF is designed to be used as a array formula (committed with Ctrl+Shift+Enter, not just plain Enter)
so select your 5 cells and write formula
and remember - array formula (CSE)!Code:=RandSum(100)
The UDF code is:
Code:Function RandSum(requiredsum As Double) As Variant() ' (c) Kaper 2014, www.klubexcela.pl, used in: ' www.mrexcel.com/forum/excel-questions/793849-about-random-number-generator-function.html ' and www.klubexcela.pl/home/kategorie-artykulow/14-porady-excel/porady-vba/stat.html Dim myresult As Variant, j As Long, i As Long, mysum As Double, myrows As Long, mycolumns As Long Application.Volatile myrows = Application.Caller.Rows.Count: mycolumns = Application.Caller.Columns.Count Randomize ReDim myresult(1 To myrows, 1 To mycolumns) For j = 1 To mycolumns For i = 1 To myrows myresult(i, j) = Rnd mysum = mysum + myresult(i, j) Next i, j mysum = requiredsum / mysum For j = 1 To mycolumns For i = 1 To myrows myresult(i, j) = myresult(i, j) * mysum Next i, j RandSum = myresult End Function
Last edited by Kaper; Jul 25th, 2014 at 11:04 AM.
Normalizing the sum of N random numbers to achieve a given total dramatically affects the distribution of the resulting summands.
N numbers that total 100 (or whatever) have N-1 degrees of freedom, not N. IMO, a better alternative is to take a piece of string of the desired length and make N-1 cuts. Here's a simple example:
A B C 1 Total Len 100.00B1: Input 2 Start 0.00B2: =0 3 Cut 1 33.81B3: =$B$1*RAND() 4 Cut 2 55.27B4: =$B$1*RAND() 5 Cut 3 21.73B5: =$B$1*RAND() 6 Cut 4 97.61B6: =$B$1*RAND() 7 8 Num 1 21.73B8: =SMALL($B$1:$B$6, ROWS(B$8:B8) + 1) - SMALL($B$1:$B$6, ROWS(B$8:B8)) 9 Num 2 12.08 10 Num 3 21.46 11 Num 4 42.35 12 Num 5 2.39
Done in that fashion, if x = (length of a given piece) / (total length), then
PDF(x) = (N-1)(1-x)^(N-2)
The approach can be modified to have a minimum value for each number, and to constrain to integer or other desired precision.
Last edited by shg; Jul 25th, 2014 at 01:53 PM.
... of course that would not be correct for the age bands of a random sample of the population.
Hi shg,
Nice concept, and indeed generated numbers will be wider distributed this way, but ... how about skewness?
In "normalization" approach numbers are symetrically distributed, while in "subdivisions length" there is strong positive skewness.
Not going into theoretical calculations I made simple experiment. For "our case", (5 random numbers summing to 100) I made 30 runs generating 10000 sets each run and of course in both cases average is 20, but stdev (for whole set, so I used STDEV.P, but of course similar tendency would be observed with STDEV)
your: ca.15.5, my: ca.10.6
skevness
your: ca. 0.8, my: 0.00
(As we went so far to 3rd moment, why not go for 4th): kurtosis
your: ca. 0.45, my: ca.-0.45
In general terms - not so easy to judge which is better, both not so close to "gaussian" 0 - your: narrower peek (on left from avg) and long positive tail, my close to uniform distribution from 0 to 2*avg with short positive tail. I'd of course opt for mine
Like this thread? Share it with others