Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: About Random number generator function

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by Ashwini Lambture; Jul 24th, 2014 at 08:39 AM.

  2. #2
    Board Regular Kaper's Avatar
    Join Date
    Mar 2014
    Location
    Warsaw, Poland
    Posts
    227
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular mrmmickle1's Avatar
    Join Date
    May 2012
    Location
    Charlotte, NC
    Posts
    2,453
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

  4. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,142
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: About Random number generator function

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

    Quote Originally Posted by Kaper View Post
    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. #5
    New Member
    Join Date
    Apr 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    New Member
    Join Date
    Apr 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: About Random number generator function

    Quote Originally Posted by Kaper View Post
    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. #7
    Board Regular Kaper's Avatar
    Join Date
    Mar 2014
    Location
    Warsaw, Poland
    Posts
    227
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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:
    ' 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.

  8. #8
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,208
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    10 Thread(s)

    Default 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.
    Last edited by shg; Jul 25th, 2014 at 01:53 PM.

  9. #9
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,208
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    10 Thread(s)

    Default 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. #10
    Board Regular Kaper's Avatar
    Join Date
    Mar 2014
    Location
    Warsaw, Poland
    Posts
    227
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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