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

Question

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?

Answer 1

This might be not a good solution for a large dataset as is your intent. Probably Excel is not the most efficient tool for that. Anyway here is a possible approach.

In cell A1, put the following formula:

=LET(maxNum, 10, trialNum, 5, maxRep, 20, event, 3, cols, SEQUENCE(trialNum,1),
  rows, SEQUENCE(maxRep, 1), rArr, RANDARRAY(maxRep, trialNum, 1, maxNum, TRUE),
  groupSize, 10, startGroups, SEQUENCE(maxRep/groupSize, 1,,groupSize),
  GROUP_PROB, LAMBDA(matrix,start,end, LET(result, BYCOL(matrix, LAMBDA(arr,
    LET(idx, IFERROR(XMATCH(event, arr),0), IF(AND(idx >= start, idx <= end), 1, 0)))),
    AVERAGE(result))),
  HREDUCE, LAMBDA(arr, LET(idx, XMATCH(event, arr),
    IF(ISNUMBER(idx), FILTER(arr, rows <= idx),event &" not found"))),
  trials, DROP(REDUCE(0, cols, LAMBDA(acc,col, HSTACK(acc,
    HREDUCE(INDEX(rArr,,col))))),,1),
  dataBlock, VSTACK("Trials", trials),
  probBlock, DROP(REDUCE(0, startGroups, LAMBDA(acc,ss,
    VSTACK(acc, LET(ee, ss+groupSize-1, HSTACK("%-Group "&ss&"-"&ee,
      GROUP_PROB(trials, ss, ee))
    ))
  )),1),
  IFERROR(HSTACK(dataBlock, probBlock), "")
)

And here is the output:

Explanation

We use LET for easy reading and composition. We first define the parameters of the experiment:

• maxNum, the maximum random number to be generated. The minimum will be 1.
• trialNum, the number of trials (in our case the number of columns)
• maxRep, the maximum number of repetitions in our case the number of rows.
• rows and cols rows and columns respectively
• event, our successful event, in our case 3.
• groupSize, The size of each group for calculating the probability of each group
• startGroups The start index position of each group
• rArr, Random array of size maxRep x trialNum. The minimum random number will be 1 and the maximum maxNum. The last input argument of RANDARRAY ensures it generates only integer numbers.

GROUP_PROB is a user LAMBDA function to calculate the probability of our successful event: number 3 was generated.

LAMBDA(matrix,start,end, LET(result, BYCOL(matrix, LAMBDA(arr,
 LET(idx, IFERROR(XMATCH(event, arr),0), IF(AND(idx >= start, idx <= end), 1, 0)))),
    AVERAGE(result)))

Basically, for each column (arr) of matrix, finds the index position of the event and check if the index position belongs to the reference interval: start, end, if so return 1, otherwise 0. Finally, the AVERAGE function serves to calculate the probability. If the event was not generated, then it counts as 0 too.

We use the DROP/REDUCE/VSTACK or HSTACK pattern. Please check the answer to the question: how to transform a table in Excel from vertical to horizontal but with different length provided by @DavidLeal.

HREDUCE user LAMBDA function filters the rArr until the event is found. In case the event was not found, then it returns a string indicating the event was not found.

The name probBlock builds the array with all the probability groups