This article describes 11 methods that present you with different ways to use Random Numbers. Not all of them are useful for Monte Carlo Simulation. But they can be used in other applications.

Click Here to download the workbook with the 11 worksheets and a Word Document with the explanations below.

The worksheets contain the following (including credits). Procedures are shown in each.

1) RandBetween with Fractional Values

In the past, Excel was restricted to RAND() and RANDBETWEEN(). RAND() generated a uniformly distributed number between 0 and 1. RANDBETWEEN() generated integers between a lower and a higher limit. It is still restricted to generating integers but is trick below which I learnt from the wonderful Leila Gharani (YouTube channel: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA

2) The use of Excel’s New Function: RANDARRAY()

3) The use of RANDARRAY() with UNIQUE Values (again from Leila’s video)

When using RAND() for very large samples, the chances of duplicate values are very low. However, one might need 10 or 15 random variables (such as when selecting staff for a team or clients for a survey). The chances of getting duplicates are much higher. This worksheet suggests a technique to get Unique values. However, this technique has its issues, as Leila Gharani found out.

4) Select M from N items using RANK

The applications of this technique are many: selecting a few clients for a survey, selecting staff for a team, selecting stock items for testing, etc. This method uses the RAND.EQ() function.

5) Select M from N items using LARGE()

This is similar to worksheet 4 above but uses the LARGE() function.

6) Shuffle a table of items

Often, in Monte Carlo Simulation, we might need to select the first N items in a table. If these are preset, we stand to bias the selection. This technique allows you to shuffle the table, then select N items.

7a) and 7b) Generate Stratified Samples based on possible categories

This technique allows you to generate a combination table given the number of entries in each field (or column). The example given generates 2000 records for a project given 5 different project names, 10 locations, 10 task types, 10 cost centers, 10 responsible persons and 12 months. This worksheet allows you to specify the possible content of each field (and their count).

The results of the Stratified Samples are in worksheet 7b.

8) Randomizing Dates

9) Select M1, M2 items from the same list of N items so that they can be assigned to one of 4 different groups x gender. Groups are given an ascending (cumulative probability) so proportions of items can be assigned to different groups.

10a and 10b) Generate a table of values with exclusions (3 different methods)

This is adapted from the YouTube clip by Doug H.

https://www.youtube.com/watch?v=kb8CUtjR7Fk&t=30s

11) Generate N Items whose sum = M

This application is adapted from Susan Murigi's YouTube clip

https://www.youtube.com/watch?v=vWYnNMZNf-Q&t=74s