Scribd
Upload
442 views

Excel Cheatsheet

The document provides an Excel cheatsheet with common Excel functions for random number generation and statistical distributions. It also describes how to use a macro to efficiently duplicate rows in Excel to perform repeated simulations through copying and pasting values rather than re-entering formulas. The macro allows the user to select the rows to duplicate and then specify the number of times to duplicate them.

Uploaded by

Boring Bland
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
442 views

Excel Cheatsheet

The document provides an Excel cheatsheet with common Excel functions for random number generation and statistical distributions. It also describes how to use a macro to efficiently duplicate rows in Excel to perform repeated simulations through copying and pasting values rather than re-entering formulas. The macro allows the user to select the rows to duplicate and then specify the number of times to duplicate them.

Uploaded by

Boring Bland
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

Excel cheatsheet

Excel Functions

• Rand(): generates a random number from the uniform distribution over the [0,1] interval
• Randbetween(a,b): generates a random integer uniformly from the set {a, a+1, a+2, … , b}
• Binom.dist(k, n, p, TRUE): evaluates the CDF of a Binomial(n,p) distribution at the value k
• Binom.dist(k, n, p, FALSE): evaluates the PMF of a Binomial(n,p) distribution at the value k
• Norm.dist(x, 𝜇, 𝜎, TRUE): evaluates the CDF of a Normal(𝜇, 𝜎) distribution at the value x
• Norm.dist(x, 𝜇, 𝜎, FALSE): evaluates the PDF of a Normal(𝜇, 𝜎) distribution at the value x
• Norm.s.dist(x): evaluates the CDF of a Normal(0,1) distribution at the value x
• Norm.inv(𝛼, 𝜇, 𝜎): evaluates the inverse CDF of a Normal(𝜇, 𝜎) distribution at the value 𝛼, i.e.
reports the value z for which the probability that a Normal(𝜇, 𝜎) random variable is less than z is
equal to 𝛼.
• Norm.s.inv(𝛼): evaluates the inverse CDF of a Normal(0, 1) distribution at the value 𝛼, i.e.
reports the value z for which the probability that a Normal(0,1) random variable is less than z is
equal to 𝛼. In class we used the notation 𝑧𝛼 = - Norm.inv(𝛼, 𝜇, 𝜎) = Norm.inv(1 − 𝛼, 𝜇, 𝜎).
• IF(Rand() < p, 1,0): generates a random number from the Bernoulli(p) distribution, i.e. outputs 1
if the random number uniformly generated between [0,1] is less than p (which occurs with
probability p) and 0 otherwise (which occurs with probability 1-p).
• IF(clause, 1, 0): takes value 1 if clause is true, takes value 0 if clause is not true
• Sum(cells): computes the sum of values in the cells indicated
• Count(cells): counts the number of cells that contain numbers in them
• Average(cells): computes the average of values in the cells indicated
• Stdev(cells): computes the empirical standard deviation of values in the cells indicated

Duplicating a row many times to generate repeated simulations

For an example of how to setup an Excel macro with VBA code to duplicate rows many times, see below:

https://www.extendoffice.com/documents/excel/3682-excel-copy-and-insert-row-multiple-times.html

Alternately, I have uploaded a file in the homework folder called “simulation_template.xlsm” which has
the macro already defined in it. When you open the file, there might be a yellow bar that appear on the
top saying “Security warning: Macros have been disabled”, in which case you need to click on “Enable
Content” to enable the macro in this file to run on your computer. (In general do not accept macros
from files you don’t trust. Or follow the instructions in the link above to add your own macro.)

To duplicate a row many times,

(1) Fill in 1 row with the relevant excel functions that you need to generate one copy of the
simulated system. Select these row/cells of interest that you would like to duplicate.
(2) Press “ALT+F11” to open the MS Visual Basic Editor. You should see the code for duplicating
rows as a module in a function called “duplicateRows()”
(3) Press “F5” to run the code. A dialog will popup asking how many times you would like to repeat
the cells you had selected. If you want a total of n simulations, then enter n-1.

You might also like