About Random number generator function

Ashwini Lambture

New Member
Joined
Apr 1, 2014
Messages
23
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 2nd 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:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
write somewhere at side (lets say in J1)
Code:
=RAND()
copy to next 4 cells right (J1:N1)
in A2:
Code:
=J1*100/SUM($J1:$N1)
and copy right to A2:E2
Enjoy !
 
Upvote 0
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
ABCDEF
1Lower Limit of Ranges
218-2425-3435-4445-5455-AboveSUM
31825354555178

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
F3=SUM(A3:E3)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

If you would like to generate Random numbers between these ranges you should use the function:

Code:
=RANDBETWEEN(BOTTOM, TOP)

ie:

Code:
=RANDBETWEEN(18, 24)
 
Last edited:
Upvote 0
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 2nd row i.e. A,B,C,D,E such that whose sum(A+B+C+D+E) will be 100 thanks in advance.
"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....

write somewhere at side (lets say in J1)
=RAND()
copy to next 4 cells right (J1:N1)[. ] in A2:
=J1*100/SUM($J1:$N1)
and copy right to A2:E2
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.
 
Upvote 0
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.
 
Upvote 0
write somewhere at side (lets say in J1)
Code:
=RAND()
copy to next 4 cells right (J1:N1)
in A2:
Code:
=J1*100/SUM($J1:$N1)
and copy right to A2:E2
Enjoy !

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.
 
Upvote 0
@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-artykulow/14-porady-excel/porady-vba/udf/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
Code:
=RandSum(100)
and remember - array formula (CSE)!

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:
Upvote 0
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.00​
B1: Input
2​
Start​
0.00​
B2: =0
3​
Cut 1​
33.81​
B3: =$B$1*RAND()
4​
Cut 2​
55.27​
B4: =$B$1*RAND()
5​
Cut 3​
21.73​
B5: =$B$1*RAND()
6​
Cut 4​
97.61​
B6: =$B$1*RAND()
7​
8​
Num 1​
21.73​
B8: =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:
Upvote 0
... of course that would not be correct for the age bands of a random sample of the population.
 
Upvote 0
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 ;)
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top