// Home / Documentation / Gallery of Distributions / Truncated normal distribution

Truncated normal distribution

Where will you meet this distribution?

Econometrics : probit model, tobit model
Marketing
“Use of the Left-Truncated Normal Distribution for Improving Achieved Service Levels” by Arvid C. Johnson
Agriculte
“Modeling Nonnegativity via Truncated Logistic and Normal Distributions: An Application to Ranch Land Price Analysis” by Feng Xu et al.

Shape of Distribution

Basic Properties

Four parameters $a, b,m,\sigma$ are required (How can you get these).
$a<b$

$\sigma>0$
Continuous distribution defined on bounded range $a\leq x \leq b$
This distribution can be symmetric or asymmetric.

Probability

Cumulative distribution function
$F(x)=\frac{1}{\Delta}\left[\Phi\left(\frac{x-m}{\sigma}\right)-\Phi(A)\right]$

where

$\Delta=\Phi(B)-\Phi(A)$

$A=\frac{a-m}{\sigma},\;B=\frac{b-m}{\sigma}$

and $\Phi(\cdot)$ is cumulative distribution function of standard normal distribution.
Probability density function
$f(x)=\frac{1}{\sigma\Delta}\phi\left(\frac{x-m}{\sigma}\right)$

How to compute these on Excel.


1
2
3
4
5
6
7
8
9

A	B
Data	Description
2.5	Value for which you want the distribution
1	Value of parameter Min
4	Value of parameter Max
3	Value of parameter M
0.9	Value of parameter Sigma
Formula	Description (Result)
=NTTRUNCNORMDIST(A2,A3,A4,A5,A6,TRUE)	Cumulative distribution function for the terms above
=NTTRUNCNORMDIST(A2,A3,A4,A5,A6,FALSE)	Probability density function for the terms above

Sample distribution

Function reference : NTTRUNCNORMDIST

Quantile

Inverse of cumulative distribution function
$F^{-1}(P)=\sigma\Phi^{-1}\left[\Delta P+\Phi(A)\right]+m$

where

$\Delta=\Phi(B)-\Phi(A)$

$A=\frac{a-m}{\sigma},\;B=\frac{a-m}{\sigma}$

and $\Phi(\cdot)$ is cumulative distribution function of standard normal distribution.

How to compute this on Excel.


1
2
3
4
5
6
7
8

A	B
Data	Description
0.5	Probability associated with the truncated normal distribution
1	Value of parameter Min
4	Value of parameter Max
3	Value of parameter M
0.9	Value of parameter Sigma
Formula	Description (Result)
=NTTRUNCNORMINV(A2,A3,A4,A5,A6)	Inverse of the cumulative distribution function for the terms above

Function reference : NTTRUNCNORMINV

Characteristics

Mean – Where is the “center” of the distribution? (Definition)

Mean of the distribution is given as
$m+\frac{\phi(A)-\phi(B)}{\Delta}\sigma$

where

$\Delta=\Phi(B)-\Phi(A)$

$A=\frac{a-m}{\sigma},\;B=\frac{a-m}{\sigma}$

, $\Phi(\cdot)$ and $\phi(\cdot)$ are probability density function and cumulative distribution function of standard normal distribution respectively.

How to compute this on Excel


1
2
3
4
5
6
7

A	B
Data	Description
1	Value of parameter Min
4	Value of parameter Max
3	Value of parameter M
0.9	Value of parameter Sigma
Formula	Description (Result)
=NTTRUNCNORMMEAN(A2,A3,A4,A5)	Mean of the distribution for the terms above

Function reference : NTTRUNCNORMMEAN

Standard Deviation – How wide does the distribution spread? (Definition)

Variance of the distribution is given as
$\left[1+\frac{A\phi(A)-B\phi(B)}{\Delta}-\left(\frac{\phi(A)-\phi(B)}{\Delta}\right)^2\right]\sigma^2$

where

$\Delta=\Phi(B)-\Phi(A)$

$A=\frac{a-m}{\sigma},\;B=\frac{a-m}{\sigma}$

, $\Phi(\cdot)$ and $\phi(\cdot)$ are probability density function and cumulative distribution function of standard normal distribution respectively.

Standard Deviation is a positive square root of Variance.

How to compute this on Excel


1
2
3
4
5
6
7

A	B
Data	Description
1	Value of parameter Min
4	Value of parameter Max
3	Value of parameter M
0.9	Value of parameter Sigma
Formula	Description (Result)
=NTTRUNCNORMSTDEV(A2,A3,A4,A5)	Standard deviation of the distribution for the terms above

Function reference : NTTRUNCNORMSTDEV

Skewness – Which side is the distribution distorted into? (Definition)

Skewness of the distribution is given as
$-\frac{1}{V^{3/2}}\left[2\Delta_0+(3\Delta_{1}-1)\Delta_0+\Delta_2\right]$

where

$z(x)=\frac{\phi(x)}{\Delta}$

$\Delta_k=B^kz(B)-A^kz(A)$

$V=1-\Delta_1-\Delta_0^2$

$\Delta=\Phi(B)-\Phi(A)$

$A=\frac{a-m}{\sigma},\;B=\frac{a-m}{\sigma}$

, $\Phi(\cdot)$ and $\phi(\cdot)$ are probability density function and cumulative distribution function of standard normal distribution respectively.

How to compute this on Excel


1
2
3
4
5
6
7

A	B
Data	Description
1	Value of parameter Min
4	Value of parameter Max
3	Value of parameter M
0.9	Value of parameter Sigma
Formula	Description (Result)
=NTTRUNCNORMSKEW(A2,A3,A4,A5)	Skewness of the distribution for the terms above

Function reference : NTTRUNCNORMSKEW

Kurtosis – Sharp or Dull, consequently Fat Tail or Thin Tail (Definition)

Kurtosis of the distribution is given as
$\frac{1}{V^2}\left[-3\Delta_0^4-2\Delta_0^2(3\Delta_1+1)-4\Delta_2\Delta_0-3\Delta_1-\Delta_3+3\right]-3$

where

$z(x)=\frac{\phi(x)}{\Delta}$

$\Delta_k=B^kz(B)-A^kz(A)$

$V=1-\Delta_1-\Delta_0^2$

$\Delta=\Phi(B)-\Phi(A)$

$A=\frac{a-m}{\sigma},\;B=\frac{a-m}{\sigma}$

, $\Phi(\cdot)$ and $\phi(\cdot)$ are probability density function and cumulative distribution function of standard normal distribution respectively.

How to compute this on Excel


1
2
3
4
5
6
7

A	B
Data	Description
1	Value of parameter Min
4	Value of parameter Max
3	Value of parameter M
0.9	Value of parameter Sigma
Formula	Description (Result)
=NTLOGNORMKURT(A2,A3,A4,A5)	Kurtosis of the distribution for the terms above

Function reference : NTTRUNCNORMKURT

Random Numbers

Random number x is generated by inverse function method, which is for uniform random U,
$x=\sigma\Phi^{-1}\left[\Delta U+\Phi(A)\right]+m$

where

$\Delta=\Phi(B)-\Phi(A)$

$A=\frac{a-m}{\sigma},\;B=\frac{a-m}{\sigma}$

, $\Phi(\cdot)$ and $\phi(\cdot)$ are probability density function and cumulative distribution function of standard normal distribution respectively.

How to generate random numbers on Excel.


1
2
3
4
5
6
7

A	B
Data	Description
1	lower limit of support
4	upper limit of support
3	Value of parameter M
0.9	Value of parameter Sigma
Formula	Description (Result)
=NTRANDTRUNCNORM(100,A2,A3,A4,A5,0)	100 truncated normal deviates based on Mersenne-Twister algorithm for which the parameters above

Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A7:A106 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER.

NtRand Functions

If you already have parameters of the distribution
- Generating random numbers based on Mersenne Twister algorithm: NTRANDTRUNCNORM
- Computing probability : NTTRUNCNORMDIST
- Computing quantile : NTTRUNCNORMINV
- Computing mean : NTTRUNCNORMMEAN
- Computing standard deviation : NTTRUNCNORMSTDEV
- Computing skewness : NTTRUNCNORMSKEW
- Computing kurtosis : NTTRUNCNORMKURT
- Computing moments above at once : NTTRUNCNORMMOM
If you know mean and standard deviation of the distribution
- Estimating parameters of the distribution:NTTRUNCNORMPARAM

Reference

Wikipedia – Truncated normal distribution

| To the Top

Comments are closed.

Truncated normal distribution

Where will you meet this distribution?

Shape of Distribution

Basic Properties

Probability

Quantile

Characteristics

Mean – Where is the “center” of the distribution? (Definition)

Standard Deviation – How wide does the distribution spread? (Definition)

Skewness – Which side is the distribution distorted into? (Definition)

Kurtosis – Sharp or Dull, consequently Fat Tail or Thin Tail (Definition)

Random Numbers

NtRand Functions

Reference

Download

Willing to generate Random Numbers in Excel?

News

Documentation

Links

Powered By