Thanks:  0
Likes:  0

1. ## About Random number generator function

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.

2. ## Re: About Random number generator function

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 !

3. ## Re: About Random number generator function

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:

Code:
`=RANDBETWEEN(BOTTOM, TOP)`
ie:

Code:
`=RANDBETWEEN(18, 24)`

4. ## Re: About Random number generator function

Originally Posted by Ashwini Lambture
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....

Originally Posted by Kaper
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.

5. ## Re: About Random number generator function

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.

6. ## Re: About Random number generator function

Originally Posted by Kaper
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.

7. ## Re: About Random number generator function

@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
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:
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```

8. ## Re: About Random number generator function

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.

9. ## Re: About Random number generator function

... of course that would not be correct for the age bands of a random sample of the population.

10. ## Re: About Random number generator function

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•