+ Reply to Thread
Results 1 to 8 of 8

Generating a list of numbers

  1. #1
    Registered User
    Join Date
    07-20-2018
    Location
    Nova Scotia
    MS-Off Ver
    Excel for Mac 16.14.1
    Posts
    6

    Generating a list of numbers

    Hi - here is my question.

    I have a list of numbers from which I would like to generate a random draw, however the numbers are typically drawn with a particular frequency (not of equal probability).

    For example: number 1 is drawn with 0.05 (out of 1) probability, 2 is drawn 0.1 prob, 3 is 0.15, 4 is 0.2, 5 is 0.05, 6 is 0.05, 7 is 0.12, 8 is 0.08, 9 is 0.01 and 10 is 0.19.

    So I multiplied the probability by 100 to get a whole number, which would equal draws out of 100, and then generate a list of numbers based on these whole numbers - instead of 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 (which are all of equal probability), the list would be weighted like this: 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, etc... in a list of 100 numbers. Then I could use a randbetween(1, 100) generate a list and use INDEX to get the corresponding drawn number.

    Is there a way to generate that column of 100 numbers? Or if it is 1000 numbers, is there a function to generate these numbers?

    I hope I am explaining this properly.

    Cheers,

    Robin

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Generating a list of numbers

    Those probabilities add up to 1.09:

    A
    B
    1
    Num
    Prob
    2
    1
    5%
    3
    2
    10%
    4
    3
    15%
    5
    4
    20%
    6
    5
    5%
    7
    6
    5%
    8
    7
    12%
    9
    8
    8%
    10
    9
    10%
    11
    10
    19%
    12
    109%


    If you fixed that,

    A
    B
    C
    D
    E
    F
    1
    Num
    Prob
    Cumu
    Rand
    2
    1
    5%
    0%
    C2: =SUM(C1,B1)
    4
    E2: =MATCH(RAND(), $C$2:$C$11)
    3
    2
    10%
    5%
    3
    4
    3
    15%
    15%
    4
    5
    4
    20%
    30%
    2
    6
    5
    5%
    50%
    7
    7
    6
    5%
    55%
    6
    8
    7
    12%
    60%
    4
    9
    8
    8%
    72%
    4
    10
    9
    10%
    80%
    7
    11
    10
    10%
    90%
    7
    12
    100%
    4
    13
    7
    14
    2
    15
    6
    16
    8
    17
    1
    18
    10
    19
    2
    20
    7
    21
    2
    22
    3
    23
    2
    24
    4
    Last edited by shg; 07-20-2018 at 04:18 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Generating a list of numbers

    Hi Robin

    This is fun.


    This is your data as I understand it.

    No Probability out of 1.
    1 0.05
    2 0.1
    3 0.15
    4 0.2
    5 0.05
    6 0.05
    7 0.12
    8 0.08
    9 0.01
    10 0.19

    I modified that to give the probability out of 100.

    1 0.05 5
    2 0.1 10
    3 0.15 15
    4 0.2 20
    5 0.05 5
    6 0.05 5
    7 0.12 12
    8 0.08 8
    9 0.01 1
    10 0.19 19

    I then use the rept function to create a string of numbers eg: Rept("1," 5) returns "1, 1, 1, 1, 1,"

    1 0.05 5 1, 1, 1, 1, 1,
    2 0.1 10 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
    3 0.15 15 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3,
    4 0.2 20 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,
    5 0.05 5 5, 5, 5, 5, 5,
    6 0.05 5 6, 6, 6, 6, 6,
    7 0.12 12 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7,
    8 0.08 8 8, 8, 8, 8, 8, 8, 8, 8,
    9 0.01 1 9,
    10 0.19 19 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,


    I then used the concat function to return my string of numbers

    1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 8, 8, 8, 8, 8, 8, 8, 8, 9, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10


    So we can now use Randbetween(1,100) to return a number between q and 100

    And Choose(5,1,2,3,4,5,6,7,8,9,10) to select a random number as specified.

    =CHOOSE(RANDBETWEEN(1,100),1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 8, 8, 8, 8, 8, 8, 8, 8, 9, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10)
    Attached Files Attached Files
    Last edited by mehmetcik; 07-20-2018 at 04:42 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Generating a list of numbers

    Quote Originally Posted by mehmetcik View Post
    =CHOOSE(RANDBETWEEN(1,100),1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 8, 8, 8, 8, 8, 8, 8, 8, 9, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10)
    Assuming your formula works.
    It can be shorter:
    Please Login or Register  to view this content.
    Quang PT

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Generating a list of numbers

    Thanks Bebo, never seen that done.

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Generating a list of numbers

    Thanks Bebo, never seen that done.

    Would this be a better match?

    =LOOKUP(RANDBETWEEN(1,100),{1;6;16;31;51;56;61;73;81;82;101},{1;2;3;4;5;6;7;8;9;10})

  7. #7
    Registered User
    Join Date
    07-20-2018
    Location
    Nova Scotia
    MS-Off Ver
    Excel for Mac 16.14.1
    Posts
    6

    Re: Generating a list of numbers

    That is very elegant! Thanks a bunch!

    Robin

  8. #8
    Registered User
    Join Date
    07-20-2018
    Location
    Nova Scotia
    MS-Off Ver
    Excel for Mac 16.14.1
    Posts
    6

    Re: Generating a list of numbers

    Thanks everyone for sharing. This is what I was thinking.

    I'm in Canada; we have three lotteries here (the Lotto Max - 7/49 with a bonus number; the 6/49 - 6/49 with a bonus number, and the Daily Grand - 5 / 49 with a bonus number between 1-7).

    The Lotto Max has been going on since 2009, the 649 since 1996 and the DG since 2016. The numbers are available for all three from their inception. They use balls for this. I did a calculation, ranking the frequency of a drawn ball over time, and there are hot and cold balls (insert all puns here...!). If you take the average rank of the lotto picks for any week (so if the numbers 5,17,19,25,31,38 and 46 had ranks of 38th, 3rd, 18th, 25th, 43rd, 27th and 40th), the mean for that week is a rank of 28 (out of 49). Over the years, the mean of those means should ideally be around 24.5 (and for the 6.49 it's 23.9, the LM it's 24.4 and the DG it's 23). So it is pretty random.

    However, because it's boring to just be random. In my imagination, unless they are changing the balls every single time to entirely new balls, and not reusing any ball to be the same numbered ball over time, then it's not really actually purely random. Balls are physical and have uneven weights. They might be dinged funny. Who knows? Maybe there's a reason ball 6 is the ball that gets drawn the most.

    Rather than pick a number from 1 - 49 at random (let's assume that computers actually generate a truly random number, though you could argue you should do that at an entirely different computer or lotto terminal each time) with each number having equal frequency of getting picked, I wanted to pick numbers each weighted with their draw frequency for each game.

    However, what I did was emulate what would happen if the first ball fell. The next to fall has to be "randomized" with 48 remaining balls, each with their frequencies, and so on for each ball that falls - you have to do 5, 6 or 7 (and bonus number) randomizations.

    Anyhow, it was a fun exercise, and when I go to buy my lotto ticket and I lose, I can at least tell myself I tried my best to emulate the conditions in the ball hopper.

    Thanks for your help everyone.

    Robin

    Wish me luck! If I get lucky I'll post here. : )
    Last edited by Robin1; 07-21-2018 at 06:41 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pls help with randomly generating numbers from a specific grid of numbers
    By ds2020 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-19-2015, 09:16 AM
  2. [SOLVED] Generating a list with random units per sale from a list with the totals
    By Juliana33 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-05-2015, 03:45 AM
  3. Replies: 14
    Last Post: 10-24-2013, 08:03 AM
  4. Generating a list of Random numbers of random length...
    By Growl in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-19-2013, 12:10 PM
  5. Generating list of sequential numbers (sans formulas)
    By gregorybrian in forum Excel General
    Replies: 2
    Last Post: 06-21-2009, 11:22 AM
  6. [SOLVED] create self-generating numbers with letters and numbers
    By cxlough41 in forum Excel General
    Replies: 11
    Last Post: 01-03-2006, 09:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1