+ Reply to Thread
Results 1 to 15 of 15

Use VBA/Macros Excel for solving this interesting simulation question (probability)

  1. #1
    Registered User
    Join Date
    11-30-2022
    Location
    USA
    MS-Off Ver
    Windows 2019
    Posts
    4

    Use VBA/Macros Excel for solving this interesting simulation question (probability)

    Imagine that we have 100 pens in our box (each numbered from 1 to 100). We mix them and then randomly draw ten pens and put them aside. Afterwards, we put ten new pens numbered from 101-110 into the box, and then again randomly draw other ten pens and put them aside. We continue doing these draws for eight times.

    Each time we keep putting new ten pens so that the total number of pens in the box remains equal to 100. For example, after second draw we put new ten pens numbered 111-120; mix the box and randomly choose ten pens and put them aside.

    I think we need to have the following in excel.

    Pen Number
    1.
    2.
    3.
    4.
    ...
    100.

    Randomly draw 10 out of 100, and put them aside. Record the number of drawn pens ( e.g. 4, 17, 74, 66, 91, 21, 33, 90, 55, 7). Then, put new ten pens numbered 101-110. Then repeat the process once more. Record the number of drawn pens (e.g. 15, 102, 87, 91, 109, 44, 22, 103, 92, 3). The repeat the process once more. Perform eight draws in total.



    We want to know the following:

    How many pens that originally were in the box (pens numbered from 1-100) remained in the box after above-mentioned eight draws? Since the draws are random we are going to have different results during each simulation of the process.

    How many pens that were put into the box after second draw (pens numbered 101-110) remained in the box after eight draws? Since the draws are random we are going to have different results during each simulation of the process.

    How many pens that were put into the box after third (pens numbered 111-120), fourth (pens numbered 121-130) ... eight (pens numbered 171-180) draw remained in the box after eight draws? Since the draws are random we are going to have different results during each simulation of the process.



    If we can answer the above-mentioned questions, it means that we can also reveal the following:

    How many pens that originally were in the box (pens numbered from 1-100) were drawn during eight draws?

    How many pens that originally were in the box after second draw (pens numbered from 101-110) were drawn during eight draws?

    How many pens that originally were in the box after third draw (pens numbered from 111-120), fourth (pens numbered 121-130) ... eight (pens numbered 171-180) were drawn during eight draws?

    We want to create a VBA/Macros (or any other relevant) tool for performing this tasks in form of simulations. Let's say 1 million simulations and check the frequency distribution for share of pens (for instance numbered 1-100) that were drawn during eight draws and for those which remained in the box.

    I will be very thankful for your help

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,825

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)

    I'm sure you can calculate these probabilities easily with basic equations of probability. there is notghing strange, just a bit tedious work. Anyway, if you want to play with simulations - why not, the simple VBA code to generate these numbers could be:
    Please Login or Register  to view this content.
    run it in an empty workbook, empty sheet.
    it now makes 10*1 000 = 10 000 simulations and depending on a computer power takes probably just few seconds. But it does not mean the time increase will be strictly linear, neither i'm not sure if you will not hit the limits of computer memory etc.

    If you are satisfied, you can increase upper limit 10 in row
    Please Login or Register  to view this content.
    or you may just rerun the code - new results will be placed below old ones

    The good point is that the whole processes of drawing 8 times 10 from refilled set of 100 numbers are independant
    You may generate say 100 000 (for n = 1 to 100) in one file, save it and prepere next 9 files (starting from empty one) the same way.
    Finally you can just copy data from all files into one.
    Best Regards,

    Kaper

  3. #3
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,778

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)

    Cross-posted at:
    http://www.vbaexpress.com/forum/show...ation-question
    https://forums.excelguru.ca/threads/...uestion.11660/
    https://chandoo.org/forum/threads/ho...uestion.50030/
    https://forum.ozgrid.com/forum/index...tion-question/
    (and who knows where else)
    Please read Excel Forum's Cross-Posting policy in rule 8 - you know, the rules you agreed to abide by when you joined this forum: http://www.excelforum.com/forum-rule...rum-rules.html
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)

    Welcome (back) to the forum.

    Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...

    But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.

    Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:

    1) if you have cross-posted, and

    2) the URL of the cross posts.

    That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.

    So. Where ELSE have you cross-posted this request?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  5. #5
    Registered User
    Join Date
    11-30-2022
    Location
    USA
    MS-Off Ver
    Windows 2019
    Posts
    4

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)

    Hello,

    I am sorry,

    I even did not know what "cross-posting" means. I did not know that these websites are somehow related to each other. This is the first time that I am using these forums.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)

    The websites are totally UNRELATED, but some people contribute to more than one.

    The point is that you are looking for help in multiple places, potentially wasting OUR time, which is given to you FREELY.

    So I repeat my question. Where else have you crossposted this request?

    NO HELP to be offered until this is done.

  7. #7
    Registered User
    Join Date
    11-30-2022
    Location
    USA
    MS-Off Ver
    Windows 2019
    Posts
    4

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)

    The question was posted here:

    1. MrExcel
    2. VBA Express
    3. Excel Forum
    4. Stack Exchange
    5. Reddit
    6. Chandoo
    7. Excel Guru
    8. OzGrid

    I will provide URLs briefly

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)

    To be honest with you... I don't do VBA, so I will be unable to help. However, if I saw that something was copied ALL across the WWW, I would just ignore the thread and move on, as I could not be bothered checking SOOO many fora to see if you already had an answer.

    You may need to add a space into the URL, as posting URLs is srestricted until you've made about 10 posts (an anti-spam measure).

    The thread remains CLOSED until the links are provided.

  9. #9
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,778

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)


  10. #10
    Registered User
    Join Date
    11-30-2022
    Location
    USA
    MS-Off Ver
    Windows 2019
    Posts
    4

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)

    How can I increase the number of trials from 8 to 16?

    Is it

    For k = 0 To 16

    If k < 16

    ?

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)

    Open for business again... even if it was Macropod (and notably NOT the Original Poster) who provided the links...

  12. #12
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,825

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)

    Four changes are needed:
    Please Login or Register  to view this content.

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)


    Hi Kaper !

    According to your codeline dict.Add CStr(j), j I can't see any reason to convert the number to a string so why ?

  14. #14
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,825

    Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)

    Sure, it could be simply
    Please Login or Register  to view this content.
    this was a result of recycling a code from one of previous posts.

    And as Im'm coming back to this thread, lets see from just probability view (mentioned at the beginning of post #2).

    we are always drawing 10% of the box content
    after first draw we have 90 pens of group 1-100 remaining in the box (0.9 as probability of not being choosen)*100
    in the second draw we added 10 from group 101-110. So we have 90 of first group and 10 of second, select 10%, so we take (statistically) 9 of first group and 1 from second. The remaining pens will (statistically) be 9 (0.9*10) from this group and 81 (0.9*0.9*100 or 0.9^2 *100) from 1-100
    third we've added 10 from 111-120. statistically we draw 1 from this group, 0.9 from 101-110, and 8.1 from first group so in box there is (statistically) 9 newest (0.9*10), 8.1 second (0.9^2 *10) and 72.9 (0.9^3 *100) from the oldest
    4th we've added 10 from 111-120. so remaining 0.9*10 ; 0.9^2 *10; 0.9^3 * 10 and 0,9^4 * 100
    ... etc
    So after 16 draws we will have (of course 90 pens total in a box)

    18.53020189 form set 1-100
    2.058911321 from set 101-110
    2.287679245 from set 111-120
    2.541865828
    2.824295365
    3.138105961
    3.486784401
    3.87420489
    4.3046721
    4.782969
    5.31441
    5.9049
    6.561
    7.29
    8.1
    9 from the most recently added (241-250)

    See attached Zeszyt3.xlsx

    Marc L at other forum (Ozgrid i think) suggested that a table could be used insteda of dictionary, with shifting results within table - well, why not. Below is working code for such approach prepared yesterday waiting for my car to be washed :-) (again reusing some of previous code) and from the triplet: reuse-recycle-reduce, reduce is probably not the case :-) neither in previous snippet, nor in this one:

    Please Login or Register  to view this content.

    Well, I'd like to see the comment and/or feedback from MrSimulation

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Use VBA/Macros Excel for solving this interesting simulation question (probability)


    Yes like I have several times demonstrated here on ExcelForum an array is the 'box' easy fast way to go
    and like I wrote on another forum « when I need to slow down the execution then I use a Dictionary instead of an array » …

+ 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. Can this be done? (Excel Drop Down Box/List Related Question)
    By t0mps in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-27-2013, 02:17 AM
  2. Replies: 0
    Last Post: 07-20-2013, 08:15 AM
  3. Replies: 0
    Last Post: 04-07-2013, 08:50 PM
  4. help needed related to excel vba macros
    By vijrana in forum Excel General
    Replies: 3
    Last Post: 02-16-2013, 09:43 AM
  5. Need to create network related macros in excel
    By mobile_guru in forum Excel Charting & Pivots
    Replies: 14
    Last Post: 10-01-2012, 01:01 AM
  6. Replies: 2
    Last Post: 05-20-2009, 08:29 AM
  7. an interesting Excel question
    By jaccker in forum Excel General
    Replies: 2
    Last Post: 04-03-2006, 11:25 AM
  8. New Version Excel 2003 related question
    By Irshad Alam in forum Excel General
    Replies: 8
    Last Post: 08-21-2005, 09:05 AM

Tags for this Thread

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