Can Excel Generate Random Numbers?

Do you know you can generate numbers, even lists of numbers in milliseconds via Excel?

In fact, it is quite easy to make experiments, sort of hypothetical calculations with scenarios via excel thanks to two functions of excel giving random numbers:

=R A N D (  )

and

=RANDBETWEEN ( bottom, top )

Generate Numbers in Excel

Let’s say you need to assign random numbers to a group of people. It may be because you are going to give them symbolic numbers in a race. OR

• Randomly select a number of people from a mailing list,
• Choose some employees in a list to make them a random survey because you can’t do it to the whole subject universe,
• Match two different classes in a school to compete in a knowledge contest
• Make pairs of football clubs to play a match with each other with a random selection among the same level clubs.

In all these examples, you can assign random numbers to the relevant items/groups and then form What-If scenarios and other calculations to organize a cause and effect relation between them.

Let’s begin with the RAND function:

RAND function gives us evenly distributed random numbers (technically pseudo-random numbers) which are greater than or equal to 0 and less than 1.

The syntax for RAND function is:
=RAND (  )

For example, if you look at the 1st example sheet, you will see the random number in cell A2 generated via RAND function.

Please notice that whenever you type something else into any cell (it may be a number, a letter or a symbol, anything), or you delete a value from another cell,or you refresh the page via F9 or you press enter when your mouse is in the cell A2,  the value in the cell A2 changes automatically. This is because the cells involving RAND fuctions are dynamic so that the worksheet is recalculated and a new random number is given each time. However this may be annoying and also may be disturbing your job if you want stable numbers to make your calculation. So, it is possible to disable automatic recalculation. Please follow these guidelines:

File > Options > Formulas and change Workbook Calculation to Manual

(This is valid for RANDBETWEEN function, as well)

Please note that you should be very careful while disabling this automatization because your other  formulas may get harmed in consequent.

You can also get rid of automatic recalculation in your table via another way. You can Copy the column that has random numbers you’ve just generated via RAND function, make a right click, choose Paste Special and then Values. In this way, since you will no longer have the formula, the numbers will not change automatically.

(This is valid for RANDBETWEEN function, as well)

If you want not only a single random number, but a list of random numbers, you can create 1 random number via RAND function in one cell and then use the fill handle to drag the cell down.  Please check the 2nd example sheet.

(This is valid for RANDBETWEEN function, as well)

If you want to get a list of random number in just one formula, selecting the cells  in the column and writing the formula is not enough, tough. First; you need to select the cells within that you want the list to appear, then you need to type the formula =RAND() , after that you need to click  CTRL + Enter  instead of  just ENTER.

(This is valid for RANDBETWEEN function, as well)

For further explanation of RAND function, you may visit our blog post.

If you decide that you need random numbers, but not in the range between 0 and 1 , but in any other range, you can use the RANDBETWEEN function. Hence, RANDBETWEEN function gives you even more option than RAND function when generating random numbers!

The syntax for RAND function is:
=RANDBETWEEN (bottom, top)

Bottom: The smallest number that the function can give

Top: The highest number that the function can give

Both the bottom and the top number can show up in the list.

Please note that the RAND function gives us a decimal number (because it needs to be between 0 and 1), while the RANDBETWEEN number gives us an integer. Please chech the 3rd example sheet to see the formula in detail. Let’s say you want to get a random number between 1 and 100:

However, if you write the same formula and do not click ENTER, but instead click F9 button, you will see that the formula switches into a stable number and it no longer changes. So, this is a short-cut to make a copy-paste special to values.  Please see the example 4th sheet and try on your own:

Let’s take an example

You have a group of employee and want to draw lots among 5 of them who was the top sales and then you will give a prize to the winner of the draw.

This is the group:

If you sort their sales scores from the biggest to the smallest via Data -> Filter -> Sort from biggest to smallest, you get;

Let’s choose someone randomly among the 5 of them who has top sales using MIN and MAX functions of excel and also the RANDBETWEEN (please look at example 5 sheet to understand in detail) :

The formula chose Mike as the winner by giving us the employee number 2.

Please note that we put another formula called VLOOKUP into the cell F and G to retrieve corresponding employee name and sales score:

If you want to learn more about how to use VLOOKUP function, check this guide out.

Important note: If your range used in RANDBETWEEN function is bigger than the range in your list, you may get duplicate numbers or errors. For instance, let’s say from the last exercise, you chose the range of RANDBETWEEN function from 1 to 10, yet in your main list you don’t have the employees whose numbers are 8, 9, or 10. That’s why when you drag the formula down the cells, and the formula gives the number 9, you get errors in VLOOKUP function.

For further explanation of RANDBETWEEN function, you may visit our blog post.

Running Simulations in Excel

Simulations are used to imitate a situation that has several variables and certain scenarios. Mostly, a mathematical model is necessary to imitate a system, a situation or a process. In this way, it becomes visible how the system operates, which variable affects how and of course the outcomes of particular actions.

In business life, you can use simulations to solve various problems such as to decide which salesperson should take how much bonus according to his/her sales and how the amount of bonus given affects the financials of the company or you can calculate the amounts of ingredients of a product that you will be producing and how the of amounts added affects the cost of goods sold and consequently how much profit you will make according to different sales forecasts. Also, you can compute our financial risk depending on the amount of production and sales via simulations creating a formula with the variables of the situation and to imitate the probabilities of the future.

Creating inputs

To establish a simulation in Excel you need to start with creating right inputs so that whenever you run the simulation, you should get new random variables which will be used as inputs to the calculation model. If we run the simulations enough number of times, the calculation becomes more meaningful because the randomness between results gets smaller. As you know before, we use Excel’s RAND or RANDBETWEEN functions to generate random variables.

We know that we can create random numbers via these two functions and we can renew them simply by pressing F9, however, to form a real-life scenario, we need to generate numbers in accordance with a probability distribution. So, the system computes a different result that is likely to appear according to the variables and inputs.

Excel has functions to calculate probability distributions easily. You can find a list them here:

• Normal: DIST, INV
• Standard normal: DIST, NORM.S.INV
• t-distribution: DIST, INV
• F-distribution: DIST, INV
• Chi-square: DIST, INV
• Lognormal: DIST, INV
• Binomial: DIST, INV
• Hypergeometric: DIST
• Beta: DIST, INV
• Gamma: DIST, GAMMA.INV
• Exponential: DIST
• Weibull: DIST
• Poisson: DIST
• Negative binomial: DIST

One of the most popular distributions is the normal distribution. You can use NORM.INV fuction for that. The syntax of the function is:

= NORM.INV(probability,mean,standard_dev)

Don’t forget, to randomize results, we include RAND function into the NORM function.

Such as:
= NORM.INV(RAND(),mean,standard_dev)

The mean and standard deviation should be meaningful according to our input data. For example, if we are computing the cost of a new good and including inputs such as the amount of production or amount of raw materials used, we need to base our mean and standard deviation to the ones of a similar product produced the previous year, for instance.

Just to remind you that, the syntax for the Mean function is :

=AVERAGE(numbers)

the syntax for Standard Deviation function is:

• S(numbers)
• P(numbers)
• STDDEVA(numbers)
• STDDEVPA(numbers)

Check out our Lottery Simulator and see other Excel template features. Now, it is easier to have safe lotteries thanks to Excel and you can conveniently prepare lotteries with our templates. 

Monte Carlo Simulation in Excel:

John von Neumann and Stanislaw Ulam coined the term Monte Carlo Simulation in the 1940s making a reference to Monte Carlo (in Monako) where is a place that is popular as the gambling point of European elites.

Monte Carlo Simulation is a method to calculate a huge amount of random draws and solve very complex problems. Economics, finance, physics, chemistry, engineering and supply chain are some fields that Monte Carlo Simulation is being made use of widely. As you notice, all these fields can have cases that having various variables to be considered.

For instance, in business, random and probabilistic methods can also be used to examine the complex options or evaluate risks that a company fails to pay its debts.

Sometimes we have a complex problem and it is impossible to solve by direct computation. We form a mathematical calculation method which includes a number of iterations which leads a simulation of normal distribution. Especially risk calculating systems and forecasting models use this method a lot.

To create a Monte Carlo simulation, we need to take the key variables of a problem and form a probability distribution after a number of samples are reached. As an example, let’s see a game of dice model in 6 phases.

Let’s say we roll 3 dices for 3 times and all dices have 6 sides.

Let’s say the rules are:

• If the total of dices is: 7 or 11; the player wins the game.
• If the total of dices is: 3, 4, 5, 16, 17, or 18; the player loses the game.
• If the total of dices is anyhing else than these: the player gets one right to roll the dices again.

(Important: Do not forget that we need 5.000 results to form a Monte Carlo simulation. Also, a data table is very useful while generating the results.)

Phase 1 : Dice Rolling

The first thing we need to do is to form a range of data let’s say for 50 rolls. We need to use RANDBETWEEN (1,6) function to get new random results whenever we press F9 or refresh the page.

We need to calculate the sum of the total results as  “outcome” in another cell.

Phase 2: Range of Outcomes

To develop possible outcomes, we need to form a range of data. So, we develop a 3 column data range. In the first column, we have the numbers from 3 to 18 representing the sum of total dices from rolling them 3 times.

3 is the minimum number because; 1 + 1 + 1 = 3

18 is the maximum number because; 6 + 6 + 6 = 18

For the cells 1 and 2, we should get the results as N/A because getting 1 or 2 is impossible in these calculations.

In the second column, we should see the possible outcomes after 1^st round as Win, Lose or Re-roll depending on the result on the first column (sum of total dices).

In the third column, we should find the possible results after following rounds. To achieve this, we can use IF function of excel. This ensures the ultimate outcome as we win or re-roll etc.

Win or Lose are the ultimate outcomes. We need to roll again and again until we get these results.

Phase 3: Conclusions

Now, we should find the results of 50 rolls. In order to reach it, we can use INDEX function. Thanks to this, the results get aligned with each other; for example, if we roll 8, we need to roll again.

We can make use of OR function of excel and IF function including with in INDEX function to create a conditional situation. For instance, if the result of the previous round is Win or Lose, the system should stop rolling.

Phase 4: Number of Dice Rolls

In order to make sure that Excel requires a re-roll and adds the number from extra round, we can make use of its COUNTIF function. In this way, we can calculate the number of dice rolls which is necessary to reach a conclusion as Win or Lose.

Phase 5: Simulation

Now, let’s calculate the results of different simulations. Remember you need 5000 simulations for a Monte Carlo simulation.

We need 3 columns. In the first one, we have figure 5000. In the second column, we need the results after 50 rolls. Lastly, in the third column, we need the number of dice rolls before getting the ultimate outcomes Win or Lose.

So now, ne need to create a sensitivity analysis table. We can generate it by using a feature table or a data table. In this sensitivity analysis table, we will put the numbers of events from 1 to 5000. We can put these results into any cells having enough empty following cells. We do this so as not to d-harm any formulas in other cells.

Phase 6: Probability

Now, we deserved to get the ultimate results! This is the phase to calculate the results of Win or Lose. We use the COUNTIF function of excel to make sure that the formula counts the number of Win or Lose and then divides it by the toal number of event, which is 5000. Hence, we get the result that the probability of getting a Win outcome is 73.2 % and the probability of getting a Lose outcome is 26.8%.

Chapter 3: Random Number Generator Excel Template:

As you may already know, Excel has statistical functions for probability distributions. These functions can be used in combination with RAND function; such as S.DIST , T.INV or GAMMA.IMV etc.

If you prefer to use our Random Number Generator templates, you can generate numbers according to the distribution type you have selected in a preprepared sheet. You can create the list easily according to features you want it to have and also you may sort or randomize it many times as you wish, you can also shuffle it with characters that you want such as letters, numbers, words etc. Very simple, easy and quite useful system!

To learn about our ready-to-use Random Generator template

You don’t think these templates suits for your needs? You can make use of our custom services to make more complex templates or reports prepared for you.