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

Truncated normal distribution

Shape of Distribution

Basic Properties

• Four parameters $a, b,m,\sigma$ are required (How can you get these).
$a
$\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

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