EXCEL Functions



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

[pic]

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

[pic]

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.

[pic]

=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.

[pic]

Poisson Distribution

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

[pic]

=POISSON.DIST(x, λ, 1) Probability of at most x outcomes when X~Poisson(λ)

[pic]

Hypergeometric Distribution

=HYPGEOM.DIST(x, n, k, N, 0)

Probability of x successes in n Trials in population with k Successes in N elements

[pic]

=HYPGEOM.DIST(x, n, k, N, 1)

Probability of at most x successes in n Trials in population with k Successes in N elements

[pic]

II. Common Continuous Distributions

Exponential Distribution

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

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

[pic]

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

[pic]

Gamma Distribution

(Exponential (α’1, β’θ) and Chi-square (α’ν/2, β’2) are special cases)

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

[pic]

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

[pic]

=GAMMA.INV(p, α, β) 100pth percentile [pic]

[pic]

Normal Distribution

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

[pic]

=NORM.DIST(x, μ, σ, 1) Normal cumulative distribution function [pic] [pic]

To obtain [pic] enter: =1 – norm.dist(x, μ, σ, 1)

=NORM.INV(p, μ, σ) 100pth percentile [pic]

[pic]

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

Chi-Square Distribution

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

[pic]

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

[pic]

=CHISQ.DIST.RT(x, v, 1) P(X ≥ x) when X~χ2v Useful in Hypothesis Testing

[pic]

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

[pic]

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

[pic]

Beta Distribution

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

[pic]

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

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

[pic]

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

[pic]

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:

[pic]

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

[pic]

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

[pic]

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

[pic]

Weibull Distribution

=WEIBULL(x, α, β, 0) Weibull probability density function

[pic]

A second commonly used parameterization is: [pic]

Then use: =WEIBULL(x, α, θ 1/α, 0)

=WEIBULL(x,α,β, 1) Weibull cdf

[pic]

Student’s t-Distribution

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

[pic]

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

[pic]

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

[pic]

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

[pic]

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

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download