Dec 15

Generating Bernoulli random variable (coin) in Excel - Exercise 2.1

Generating the Bernoulli random variable is very easy and is better than actually tossing a coin many times. This video illustrates Exercise 2.1 from my book: how to use Excel RAND() and IF functions to simulate the coin in Excel

Simulation steps

  1. RAND() (with empty parentheses) is the Excel function to generate a uniformly distributed random variable on the interval (0,1). If you enter the command =RAND() in two different cells, the generated variables will be independent. The generated values will be recalculated each time you change the spreadsheet, unless the Recalculate option is off.
  2. The IF function has three arguments. The first argument is a condition. If the condition is satisfied, the IF function supplies the second argument. If not, the third argument is produced. For example, the command =IF(RAND()>0.3,1,0) simulates an unfair coin: the value 1 is taken 70% of the time.

See how this exercise can be extended to illustrate the law of large numbers.

The same RAND() command can be used to visualize the dependence of the slope and intercept estimators on the sample.

The RAND() command is also used to generate normally distributed variables. A similar construction allows one to simulate any random variable with an invertible cumulative distribution function, see Exercise 7.6 in my book.

Exercise 2.1

Exercise 2.1 - click to view video


Leave a Reply

You must be logged in to post a comment.