PDA

View Full Version : How can we use VBA/Macros Excel for solving this interesting simulation question?



MrSimulation
11-30-2022, 01:03 AM
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.


Can you please help with the solution? I tried to simulate it using VBA could not quite get what I wanted

Aussiebear
11-30-2022, 02:39 AM
Since its a multiple random draw, you will never know for sure.

macropod
11-30-2022, 05:12 AM
Cross-posted at:
Use VBA/Macros Excel for solving this interesting simulation question (probability) (excelforum.com) (https://www.excelforum.com/excel-programming-vba-macros/1394590-use-vba-macros-excel-for-solving-this-interesting-simulation-question-probability.html)
How can we use VBA/Macros Excel for solving this interesting simulation question? | Excelguru Forums (https://forums.excelguru.ca/threads/how-can-we-use-vba-macros-excel-for-solving-this-interesting-simulation-question.11660/)
How can we use VBA/Macros Excel for solving this interesting simulation question related to probability distribution? | MrExcel Message Board (https://www.mrexcel.com/board/threads/how-can-we-use-vba-macros-excel-for-solving-this-interesting-simulation-question-related-to-probability-distribution.1223471/)
How can we use VBA/Macros Excel for solving this interesting simulation question? | Chandoo.org Excel Forums - Become Awesome in Excel (https://chandoo.org/forum/threads/how-can-we-use-vba-macros-excel-for-solving-this-interesting-simulation-question.50030/)
How can we use VBA/Macros Excel for solving this interesting simulation question? - Excel VBA / Macros - OzGrid Free Excel/VBA Help Forum (https://forum.ozgrid.com/forum/index.php?thread/1232950-how-can-we-use-vba-macros-excel-for-solving-this-interesting-simulation-question/)
excel - Solving this interesting simulation question related to probability distribution - Stack Overflow (https://stackoverflow.com/questions/74624241/solving-this-interesting-simulation-question-related-to-probability-distribution)
How can we use VBA/Macros Excel for solving this interesting simulation question related to probability distribution? : excel (reddit.com) (https://www.reddit.com/r/excel/comments/z8kuno/how_can_we_use_vbamacros_excel_for_solving_this/)

Please read VBA Express' policy on Cross-Posting in Rule 3 - you know, the rules you agreed to abide by when you joined this forum: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3
Thread closed.

SamT
12-01-2022, 06:28 AM
The problem is not solvable as stated. How does one pull 0.9 pens from a pile?

Mathematically, over many iterations, a perfect distribution curve will emerge wherein exactly 10% of remaining pens in each set are removed from the pile of 100 each iteration. But first, you must solve the fractional pen issue.