стр. 34 |

119

Statistical Analysis with Excel

7.2.B INVERSE FUNCTION

This function calculates the inverse of the normal cumulative Density

Function for a userвЂ“specified mean and standard deviation.

NORMINV (probability below the X, MEAN, STANDARD DEVIATION) X

Figure 108: The inverse function for a Normal Density Function

7.2.C CONFIDENCE INTERVALS

Menu path to function: INSERT / FUNCTION / STATISTICAL /

NORMINV.

Data requirements: The data series should follow the assumed Density

Function type (Normal).

95% Confidence Interval

The Confidence Interval contains all but 2.5% of the extreme values on

each of the tails of the Density Function (Probability Density Function

(PDF)) or is the value that corresponds to 0.025 and 0.975 on the

Cumulative Density Function (CDF). The 95% Confidence Interval for a

series that follows a Normal Density Function with mean = Вµ and

standard deviation = Пѓ is defined by the results of the two inverse

120

Chapter 7: Probabiity Density Functions & Confidence Intervals

functions at these two probabilities:

вЂ” NORMINV (0.025, mean, standard deviation)

вЂ” NORMINV (0.975, mean, standard deviation) for the lower and

upper limit

90% Confidence Interval

The 90% Confidence Interval for a series that follows a Normal Density

Function with mean = Вµ and standard deviation = Пѓ is defined by the

results of the two inverse functions at these two probabilities:

вЂ” NORMINV (0.05, mean, standard deviation)

вЂ” NORMINV (0.95, mean, standard deviation) for the lower and

upper limit

Table 16: Normal Density FunctionвЂ” Formulae for 90%, 95%, and 99% Confidence limits.

Confidence

Formula for lower bound Formula for upper bound

level

NORMINV (0.05, mean, NORMINV (0.95, mean,

90%

standard deviation)9 standard deviation)

NORMINV (0.025, mean, NORMINV (0.975, mean,

95%

standard deviation) standard deviation)

Note that many books use the following symbols or phrases for the mean and standard

9

deviationвЂќ

вЂў Вµ or вЂњmuвЂќ for mean

вЂў Пѓ or вЂњsigma standard deviation/error

вЂў Пѓ2 or вЂњsigma square variance

121

Statistical Analysis with Excel

Confidence

Formula for lower bound Formula for upper bound

level

NORMINV (0.005, mean, NORMINV (0.995, mean,

99%

standard deviation) standard deviation)

STANDARD NORMAL OR ZвЂ“DENSITY FUNCTION

7.3

The Cumulative Density Function (CDF) is the integral of the function on

the right hand side in the above equation. The range of integration is

negative infinity to the Z value being studied.

CDF:

NORMSDIST (z) probability of values lying to the left of Z

Figure 109: The Normal Density Function

Menu path to function: INSERT / FUNCTION / STATISTICAL

/NORMSDIST.

Data requirements: The data series вЂ˜zвЂ™ should follow the assumed Density

Function type (Standard Normal).

122

Chapter 7: Probabiity Density Functions & Confidence Intervals

стр. 34 |