• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can we use VBA/Macros Excel for solving this interesting simulation question?

M

MrSimulation

New Member
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
 
Not such interesting, just a beginner level Excel basics VBA SSS simulation and without any desired result layout workbook sample … :rolleyes:​
​
 
FYI, cross-posted:

How can we use VBA/Macros Excel for solving this interesting simulation question? - OzGrid Free Excel/VBA Help Forum

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…
forum.ozgrid.com forum.ozgrid.com
www.mrexcel.com

How can we use VBA/Macros Excel for solving this interesting simulation question related to probability distribution?

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...
www.mrexcel.com www.mrexcel.com
 
MrSimulation
... should read Forum Rules
... from many sites
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
MrSimulation said:
If we can answer the above-mentioned questions, it means that we can also reveal the following:
Click to expand...
According to Excel basics features it is very useless - not necessary - to answer to the 'above questions' to just 'reveal the following' …​
​
 
Seems already solved on another forum (same thread on 8 forums ‼)​
with an 'over complicated' way specific to Windows only, hoping not to be used under a Mac …​
I send it to a kid Excel / VBA beginner as a training, solved in few minutes, drawns via a classic SSS VBA procedure,​
he just used basics worksheet functions without VBA for the 'reveal the following' part, yes at kid level so not such 'interesting'…​
​
 
@Marc L: You probably refer to my answer on Use VBA/Macros Excel for solving this interesting simulation question (probability) (excelforum.com)
Well, for me it's just as complicated as needed :), quite easy to read, and written also in just few minutes - uses simple dictionary structure and few loops. But of course it is just one of possible solutions. So would be nice if you show also this "classic SSS VBA procedure".

OP has (on excelforum, where I answered) info about Win 2019 in his profile. Nothing suggest Mac version. But if it would be Mac, there is simple cure: GitHub - VBA-tools/VBA-Dictionary: Drop-in replacement for Scripting.Dictionary on Mac
 
As I already shared a Mac Dictionary way on this forum section 'The Vault' or on other forums (like ExcelForum)​
or directly without any class module several times on ExcelForum but whatever under Mac or Windows​
any 'unique random' can be directly achieved without a Windows Dictionary neither a VBA Collection​
but just with a faster array as an easy Swap Shuffle System (or Shuffle Swap System whatever) 'box'​
like in this thread among many samples I shared on ExcelForum on the same 'unique random' subject …​
​
When I need to slow down the execution then I use a Dictionary instead of an array.​
​
So for this 'interesting simulation' student homework the draws just with a VBA array 'box' and​
the part 'reveal the following' with Excel basics worksheet functions like the kid did …​
​
Anyway Kaper, good match next Sunday !​
​
 
An interesting random thread :​

Creating 20 random combination of 10 numbers from 90 numbers

Hi I am setting a question paper series for practice. Refer model sheet enclosed. I have 90 questions along a column in a sheet numbered 1 to 90 in the adjacent cell. Now, there are 10 groups of questions A to J. Each group has different numbers of questions. For example A has just 2 questions...
chandoo.org
 
You must log in or register to reply here.
Back
Top