Continuous
Distributions
Using Excel
Excel Functions
You can use Excel functions for the normal distribution to find
probabilities, plot the distribution, and generate random data. The
exponential distribution has no inverse function. The uniform has
no Excel functions, but you can easily create your own formulas.
Continuous Distribution Excel Function
Normal distribution
PDF: Returns height of f(x) NORM.DIST(x, µ, σ, 0)
CDF: Returns probability P(X ≤ x) NORM.DIST(x, µ, σ, 1)
Inverse CDF: Returns x for P(X ≤ x) = α NORM.INV(α, µ, σ)
…Random data (makes k samples in R) NORM.INV(RAND(), µ, σ)
Uniform distribution a < x < b
PDF: Returns height of f(x) 1/(b-a)
CDF: Returns probability P(X ≤ x) (x-a)/(b-a)
Inverse CDF: Returns x for P(X ≤ x) = α a + α *(b-a)
…Random data (makes k samples in R) a + (b-a)*RAND()
Exponential distribution
PDF: Returns height of f(x) EXPON.DIST(x, λ, 0)
CDF: Returns probability P(X ≤ x) EXPON.DIST(x, λ, 1)
Inverse CDF: Returns x for P(X ≤ x) = α ----------------
…Random data (makes k samples in R) ----------------
Normal
Use NORM.DIST(x,mu,sigma,1) to find left-tail N(,) probability
P(X x0) and NORM.INV(alpha,mu,sigma) to find x for a left-tail area
alpha. We illustrate for = 75 and = 8.
Alpha x Formula
x P(X<=x) Formula
0.005 54.3934 =NORM.INV(0.005,75,8)
60 0.03040 =NORM.DIST(60,75,8,1)
0.010 56.3892 =NORM.INV(0.01,75,8)
65 0.10565 =NORM.DIST(65,75,8,1)
0.025 59.3203 =NORM.INV(0.025,75,8)
70 0.26599 =NORM.DIST(70,75,8,1)
0.050 61.8412 =NORM.INV(0.05,75,8)
75 0.50000 =NORM.DIST(75,75,8,1)
0.100 64.7476 =NORM.INV(0.10,75,8)
80 0.73401 =NORM.DIST(80,75,8,1)
0.250 69.6041 =NORM.INV(0.25,75,8)
85 0.89435 =NORM.DIST(85,75,8,1)
0.500 75.0000 =NORM.INV(0.50,75,8)
90 0.96960 =NORM.DIST(90,75,8,1)
Tip: Subtract from 1 Tip: Normal distribution
to get right-tail areas. is symmetric around .
Standard Normal
Use NORM.S.DIST(z0) to find left-tail N(0,1) probability P(z z0) and
NORM.S.INV(alpha) to find z for a left-tail area alpha.
z0 P(z<=z0) Formula Alpha z Formula
-3.000 0.00135 =NORM.S.DIST(-3,1) 0.005 -2.5758 =NORM.S.INV(0.005)
-2.000 0.02275 =NORM.S.DIST(-2,1) 0.010 -2.3263 =NORM.S.INV(0.01)
-1.000 0.15866 =NORM.S.DIST(-1,1) 0.025 -1.9600 =NORM.S.INV(0.025)
0.000 0.50000 =NORM.S.DIST(0,1) 0.050 -1.6449 =NORM.S.INV(0.05)
1.000 0.84134 =NORM.S.DIST(1,1) 0.100 -1.2816 =NORM.S.INV(0.10)
2.000 0.97725 =NORM.S.DIST(2,1) 0.250 -0.6745 =NORM.S.INV(0.25)
3.000 0.99865 =NORM.S.DIST(3,1) 0.500 0.0000 =NORM.S.INV(0.50)
Tip: Subtract from 1 Tip: Normal distribution
to get right-tail areas. is symmetric around zero.
Standard Normal PDF
Use NORM.S.DIST(z,0) to find the height of the standard normal
PDF. Plotting only a few points will reveal the curve. Even a bar
chart can look like a normal distribution.
z P(z) Formula
-2.80 0.00792 =NORM.S.DIST(-2.80,0)
-2.60 0.01358 =NORM.S.DIST(-2.60,0)
-2.40 0.02239 =NORM.S.DIST(-2.40,0)
-2.20 0.03547 =NORM.S.DIST(-2.20,0)
-2.00 0.05399 =NORM.S.DIST(-2.00,0)
... ... ..........
2.00 0.05399 =NORM.S.DIST(2.00,0)
2.20 0.03547 =NORM.S.DIST(2.20,0)
2.40 0.02239 =NORM.S.DIST(2.40,0)
2.60 0.01358 =NORM.S.DIST(2.60,0)
2.80 0.00792 =NORM.S.DIST(2.80,0)
Tip: Graphs show 29 points
on the standard normal PDF
in scale increments of .20.
Normal Data
Use NORM.INV(RAND(),mu,sigma) to generate random normal data.
For example, here are 30 random observations from N(75,8)
rounded to integers using =ROUND(NORM.INV(RAND(),75,8),0).
84 83 70 77 63 71
60 75 69 72 73 64
84 85 80 75 75 82
80 73 76 83 78 78
77 76 80 92 64 78
Tip: A stem-and-leaf plot
reveals the normal shape
(this one is from MegaStatTM).
Exponential
To find an exponential probability P(X x) we can use the
Excel function EXPON.DIST(x,λ,1) where λ is the mean arrival
rate per unit of time. The example below uses λ = 2.2.
x P(X<=x) Formula
0.25 0.42305 =EXPON.DIST(.25,2.2,1)
0.50 0.66713 =EXPON.DIST(.50,2.2,1)
0.75 0.80795 =EXPON.DIST(.75,2.2,1)
1.00 0.88920 =EXPON.DIST(1.00,2.2,1)
1.25 0.93607 =EXPON.DIST(1.25,2.2,1)
1.50 0.96312 =EXPON.DIST(1.50,2.2,1)
Tip: Subtract from 1 to Tip: Excel has no inverse
get right-tail areas. exponential function to find
x for a desired area.
Exponential PDF
Use EXPON.DIST(x,λ,0) to find the height of the exponential PDF.
Plotting only a few points will reveal the curve. Even a bar chart
can look like an exponential distribution.
x P(x) Formula
0.20 1.4169 =EXPON.DIST(.20,2.2,0)
0.40 0.9125 =EXPON.DIST(.40,2.2,0)
0.60 0.5877 =EXPON.DIST(.60,2.2,0)
0.80 0.3785 =EXPON.DIST(.80,2.2,0)
1.00 0.2438 =EXPON.DIST(1.00,2.2,0)
1.20 0.1570 =EXPON.DIST(1.20,2.2,0)
1.40 0.1011 =EXPON.DIST(1.40,2.2,0)
1.60 0.0651 =EXPON.DIST(1.60,2.2,0)
1.80 0.0419 =EXPON.DIST1.80,2.2,0)
2.00 0.0270 =EXPON.DIST(2.00,2.2,0)
2.20 0.0174 =EXPON.DIST(2.20,2.2,0)
2.40 0.0112 =EXPON.DIST(2.40,2.2,0)
Advice
Excel continuous distributions
Pro: The normal function is very nice.
Pro: Easy to generate random normal data.
Con: No inverse for exponential.
Tip: Common sampling distributions (Student's t, F,
chi-square) are covered in later chapters.
