EXCEL Probability Distribution Functions

Note: The = before each function name causes EXCEL to use the function as opposed to
simply typing it in as text.

I. Common Discrete Distributions

Binomial Distribution
=BINOM.DIST(x, n, p, 0) Probability of x successes in n trials with P(Success)=p

n x n!
P ( X x) p (1 p ) n x p x (1 p) n x x 0,1,..., n
x x!( n x)!

=BINOM.DIST(x, n, p, 1) Probability of at most x successes in n trials with P(Success)=p

x
n k
P( X x) p(0) p( x) p (1 p) n k x 0,1, , n
k 0 k
Geometric/Negative Binomial Distribution
=NEGBINOM.DIST(x, r, p, 0)
Probability of having x failures prior to the rth success in independent Bernoulli trials with P(Success)=p.
This is equivalent to observing the rth success on the (x+r)th trial. Geometric distribution arises when r = 1.

x r 1 r
p (1 p ) x x 0,1,2,... r 1,2,...
r 1

=NEGBINOM.DIST(x, r, p, 1)
Probability of having at most x failures prior to the rth success in independent Bernoulli trials with
P(Success)=p. Geometric distribution arises when r = 1.

x
k r 1
P ( X x) p (0) L p ( x ) p r
(1 p ) k
x 0,1,K , n
k 0 k

Poisson Distribution

=POISSON.DIST(x, , 0) Probability of x outcomes when X~Poisson()

e x
P( X x) x 0,1,...
x!
=POISSON.DIST(x, , 1) Probability of at most x outcomes when X~Poisson()

x
e k
P( X x) x 0,1,...
k 0 k!

Hypergeometric Distribution
=HYPGEOM.DIST(x, n, k, N, 0)
Probability of x successes in n Trials in population with k Successes in N elements

k N k

P ( X x) x nx 0 xk N 0 xn N
N

n

=HYPGEOM.DIST(x, n, k, N, 1)
Probability of at most x successes in n Trials in population with k Successes in N elements
k N k
x
j n j
P ( X x) 0xk N 0 xn N
j 0 N

n

II. Common Continuous Distributions

Exponential Distribution
(Hardly worth the effort. Note: must use reciprocal of mean)

=EXPON.DIST(x, 1/ , 0) Exponential Density Function

1 x /
f ( x; ) e x0 0

=EXPON.DIST(x, 1/ , 1) Exponential Cumulative Distribution Function

x
1 t /
F ( x; ) P ( X x) e dt 1 e x /
0

Gamma Distribution
(Exponential ( ) and Chi-square ( are special cases)

=GAMMA.DIST(x, , 0) Probability Density Function of Gamma( )

1
x 1e x / y
1
f ( x) x0 0 0 ( ) e y dy
( ) 0

=GAMMA.DIST(x, , 1) Cumulative Distribution Function of Gamma( )

x 1
F ( x ) P ( X x) t 0 ( )
t 1e t / dt x0

=GAMMA.INV(p, ) 100pth percentile P( X X p ) p

p P X X p
Xp 1

x 1e x / dx 0 p 1
0 ( )

Normal Distribution

=NORM.DIST(x, , 0) Normal density function f(x; )

1 x 2
f ( x; , ) exp x 0
2 2 2 2

=NORM.DIST(x, , 1) Normal cumulative distribution function P ( X X 0 )

P X x
x 1

t
2
x
exp dt P Z

2 2
2 2

To obtain P( X x) enter: =1 norm.dist(x, , 1)

=NORM.INV(p, ) 100pth percentile P( X X p ) p

 x 2
p P X X p
Xp 1
exp dx X p Z p P( Z Z p ) p

2 2 2 2

Function NORM.S.INV(p) returns the 100pth percentile of standard normal (Z) distribution, that is:
NORM.S.INV(p) = NORM.INV(p, 01)

Chi-Square Distribution
=CHISQ.DIST(x, v, 0) Chi-square Density function

1
f x; t ( /2)1e t /2 x 0; 0
/ 2
2
2

=CHISQ.DIST(x, v, 1) P(X x) when X~ 2v (Non integer is truncated)

x
1
P( X x) t ( /2)1e t /2 dt x0 0

0 2 /2
2
=CHISQ.DIST.RT(x, v, 1) P(X x) when X~ 2v Useful in Hypothesis Testing

1
P( X x) t ( /2)1e t /2 dt x0 0

x 2 /2
2

=CHISQ.INV(p , v) 100p percentile (Non integer is truncated)

XP
1
p P( X X p )
x ( /2) 1e x /2 dx
0 2 / 2
2

=CHISQ.INV.RT(p , v) 100(1-p) percentile Useful in Hypothesis Testing

1
p P( X X p ) /2
x ( / 2 ) 1e x / 2 dx
XP 2
2

Beta Distribution
=BETA.DIST(x, , ) Beta density function (0 x 1)

( ) 1
f ( x) x (1 x) 1 0 x 1 0 0
( )( )
For Beta distributions transformed to the range [A,B], use BETA.DIST(x, , , 0, A, B)

=BETA.DIST(x, , ) Beta cumulative distribution function (0 x 1)

( ) 1
x
P( X x) t (1 t ) 1 dt 0 x 1 0 0
0
( ) ( )
For Beta distributions transformed to the range [A,B], use BETA.DIST(x, , , 1, A, B)

=BETA.INV(p, , ) 100pth-percentile P(X Xp) = p

 Xp
( )
( )( ) x
1
p P( X X p ) (1 x ) 1 dx
0

For Beta distributions transformed to the range [A,B], use BETA.INV(x, , ,A,B)

Lognormal Distribution

If Y = ln(X) ~ Normal( 2) then X~Lognormal( 2) with:

1 2
/ 2 2
f ( x) e (ln( x ) ) x 0, , 0
2 x
E ( X ) e
2
/2
V ( X ) e 2 e 1
2
2

=LOGNORM.DIST(x, , 0) density function of lognormal distribution: P(Xx)

1 2 2
f ( x) e (ln( t ) ) /2
2 t

=LOGNORM.DIST(ln(x), ,1) cdf of lognormal distribution: P(Xx)

x ln( x ) (t ) 2
1 1
P ( X x)
2 2
e (ln(t ) ) / 2 dt e 2 2
dt
0 2 t 2

=LOGNORM.INV(p, ) 100pth percentile: P(X Xp) = p

Xp
1

2
/ 2 2
p P( X X p ) e (ln(t ) ) dt
0 2 t

Weibull Distribution
=WEIBULL(x, , 0) Weibull probability density function

1 ( x / )
f ( x; , ) x e x 0 , 0

 1 x /
A second commonly used parameterization is: x e

Then use: =WEIBULL(x, 0)
=WEIBULL(x, , 1) Weibull cdf

x
1 ( t / )
F ( x; , ) t e dt x 0 , 0
0

Students t-Distribution

=T.DIST(x, , 0) density function ( truncated to integer value) for t distribution P(Xx)

( 1) / 2
( 1)/ 2
t 2
f ( x) 1 x 1
( / 2)

=T.DIST(x, , 1) cdf ( truncated to integer value) for t distribution P(Xx)

( 1) / 2
( 1)/2
t 2
x
P ( X x)
( / 2)
1

dt x 1

=T.DIST.RT(x, ) upper tail area ( truncated to integer value) for t distribution

( 1) / 2
( 1) / 2
t2
P( X x) 1 dt x 0 1
x ( / 2)

=T.INV(p, ) 100pth Percentile of t-distribution

( 1)/2
( 1) / 2
Xp
t 2
p P( X X p )
( / 2)
1

dt

=T.INV.RT(p, ) 100(1-p/2)th Percentile of t-distribution (Only meaningful for p<0.5)

 ( 1)/ 2
( 1) / 2
X p /2
t 2
p P ( X p /2 X X p / 2 )
X p /2 ( / 2)
1

dt

F-Distribution

=F.DIST (x, 1, 2, 0) Density for F1,2 Distribution ( 1, 2 truncated to integers)

( 1 2 ) / 2 ( 1 / 2 )1 /2
(1 2 )/2
( 1 /2) 1 1 f
f ( x) (f) 1 x 0 1 , 2 1
( 1 / 2)( 2 / 2) 2

=F.DIST(x, 1, 2, 1) cdf for F1,2 Distribution ( 1, 2 truncated to integers)

( 1 2 ) / 2 ( 1 / 2 )1 /2
(1 2 )/2
( 1 /2) 1 1 f
x
P( X x) (f) 1 df x 0 1 , 2 1
0
( 1 / 2)( 2 / 2) 2

=F.DIST.RT(x, 1, 2) Upper tail area for F1,2 Distribution ( 1, 2 truncated to integers)

( 1 2 ) / 2 ( 1 / 2 ) 1 / 2
( 1 2 ) / 2
f
P( X x) ( f ) ( 1 / 2)1 1 1 df x 0 1 , 2 1
x
( 1 / 2)( 2 / 2) 2

=F.INV(p, 1, 2) 100pth percentile of F1,2 Distribution ( 1, 2 truncated to integers)

( 1 2 ) / 2 ( 1 / 2 )1 /2
Xp (1 2 )/2
f
p P( X X p )
0
( 1 / 2)( 2 / 2)
( f ) (1 /2) 1 1 1
2
df X p 0 1 , 2 1

=F.INV.RT(p, 1, 2) 100(1-p)th percentile of F1,2 Distribution ( 1, 2 truncated to integers)

( 1 2 ) / 2 ( 1 / 2 )1 / 2
( 1 2 ) / 2
f
p P( X X p ) ( f ) (1 / 2 )1 1 1 df X p 0 1 , 2 1
Xp
( 1 / 2)( 2 / 2) 2