How can I use simulation tool in Excel for solving the following problem related to probability?

[MrSimulation]

Trial Number 1 2 3 4 5 ........ 2000000 (two million)

Success in nth attempt 12 4 21 5 10 12

Note: Imagine throwing a dice where each outcome has probability of 1/10 (not 1/6 as it is usual for dice). For us "success" means throwing a "3". For each trial (see above) we keep throwing dice until we get "3". For example, above I assume that during first trial we threw dice 12 times and could get "3" only on 12th attempt. The same for other trials. For instance, on 5th trial we threw dice 10 times and could get "3" only on 10th attempt.

We need to simulate this for 2 million times (or lower than that, let's say 500,000 times).

Eventually we need to calculate what percent of "success" happens in interval of 10-20 tries, 1-10 tries etc.

For example, out of 2000000 trials in 60% of cases (1200000) we get "3" in between 10th and 20th attempts of throwing a dice.

Can you please help?

I performed a manual simulation, but could not create a simulation model. Can you please help?

 

[Sulprobil]

Hello,

I have stored a sample Excel VBA solution here (open and use at your own risk, but I am using an up-to-date virus scanning program):

Dropbox - File Deleted - Simplify your life

www.dropbox.com

The output of the first 10 result rows looks like:

MrExcel_How can I use simulation tool in Excel for solving the following problem related to probability.xlsm
	A	B	C	D	E
1	Number of Simulations	2.000.000		3 showed up after this many throws	How often
2				1	200034
3				2	179988
4				3	162136
5				4	145359
6				5	131237
7				6	118598
8				7	106469
9				8	96150
10				9	86116
11				10	77357
Sheet1

Regards,
Bernd

 

[MrSimulation]

Hello,

I have stored a sample Excel VBA solution here (open and use at your own risk, but I am using an up-to-date virus scanning program):

Dropbox - File Deleted - Simplify your life

www.dropbox.com

The output of the first 10 result rows looks like:

MrExcel_How can I use simulation tool in Excel for solving the following problem related to probability.xlsm
	A	B	C	D	E
1	Number of Simulations	2.000.000		3 showed up after this many throws	How often
2				1	200034
3				2	179988
4				3	162136
5				4	145359
6				5	131237
7				6	118598
8				7	106469
9				8	96150
10				9	86116
11				10	77357
Sheet1

Regards,
Bernd

Thank you so much, it was very helpful

 

[Kaper]

Well, that's pretty simple without simulation.
probability of success in first throw is p=1/10
in second we felt into (1-p) in first then we have a chance of p so is 0.09 (0.9) * 0.1
in third it is (1-p)*(1-p) *p = 0.081 and so on.

In nth throw it is (1-p)^n-1*p

So the calculated numbers (from 1 to 20 throws) assuming 2 millions trials are:

200 000
180 000
162 000
145 800
131 220
118 098
106 288
95 659
86 093
77 484
69 736
62 762
56 486
50 837
45 754
41 178
37 060
33 354
30 019
27 017

a "side obserwation" is that the above totals to 1 756 847
so in remaining 243 153 out of 2 000 000 cases - about 1/8th - we have to wait longer than 20 tosses for "the lucky number".