quant

NormalDistributionCalculationsinExcel.xlsx

Continuous Distributions

Continous Probability Distributions: data values are measured and not counted
	a probability DENSITY functions represented by mathematical functions
	probabilities are calculated within ranges and not at specific values: P(a) is theoretically 0 but Excel does estimate the height of the probability density curve at 'a'
			Shape	Range	Other
		Normal	Bell Shaped	negative infinity to infinity	mean = median
		Uniform	Rectangular & symmetric	a to b	mean = median
		Exponential	Skewed right	0 to infinity	mean>median
	Normal Distribution Benefits
		Many variables in business can be represented using a normal distribution
		Used to approximate discrete probability distributions
		can be completely defined (with it's mathematical function) with the mean and standard deviation
			N(mean, standard deviation)

Calculating Normal Distribution Probabilities with Excel
	Excel transforms Xi data values to Z scores before calculating probabilties
	the standard normal distribution is used to determine the probablities
		Use NORMDIST in Excel
	Example
		You need
		Mean=		10
		Standard Deviation =		2
			STANDARDIZE	NORMDIST	1-NORMDIST
		Xi	Z-score	P(X<Xi)	P(X>Xi)		To find probability of ranges
		7	-1.5	0.0668072013	0.9331927987		P(Xa<X<Xb)=ABS(P(X<Xb)-P(X<Xa))
		5	-2.5	0.0062096653	0.9937903347
		9	-0.5	0.3085375387	0.6914624613	Probability of age between 8 and 12 ?
							0.6826894921

=NORMDIST(Xi, mean, s, False)

=STANDARDIZE(Xi, mean, s)

Calculating data values of a normal distribution when the corresponding Z score or probabilities are given
				WHat is the X value associated with Z-Score = 1.5?
	You Need				73.75
		Mean=	70
		STandard Deviation =	2.5
				What is the X value for the distribution where p = .35 for values less than X?
						69.036698834
				What is the Z-score value for the distribution where p = .35 for values less than Z?
						-0.3853204664

=NORMINV(p, mean, s)

=NORMSINV(p)

=mean + Z(s)

Is the Data distribution approximately normal?

check the histogram

examine the descriptive statistics

use a normality plot

Plot A

Plot B

Mean and median should be near equal

bell-shaped histogram

Dots are close to line

Suggests the distribution is approximately normal in shape

Need a bell-shaped distribution to use the Empirical Rule
Age
80	Age Desciptive Statistics					Approximately:
91							68% of data values are within 1 standard deviation of the mean
77	Mean	79
85	Standard Deviation	9.5694384163					95% of data values are within 2 standard deviation of the mean
71
89							99.7% of data values are within 3 standard deviations of the mean
82
79
74
68	Question1: Approximately what percent of the age data is within 1 standard deviation unit of the mean?
103			According to the Empirical Rule, approximately 68% of the age
92			data is within 1 standard deviation above and below the mean
85
81	Question2: Within what interval are approximately 68% of the age data values? (Hint: need standard deviation of the distirbution)
82			According to the Empirical Rule
64				lower bound	69.5352344046
81				upper bound	88.6741112372
84
100
86
83
67
84
78
63
70
85
99
93
79
88
69
86
103
85
73
61
71
89
98
71
81
83
81
69
65
77
82
80
78
60
71
71
73
86
84
79
77
85
72
83
89
85
91
73
76
87
73
86
85
78
94
79
76
79
76
64
78
65
71
72
68
81
84
77
68
70
82
66
81
82
79
55
99
68
74
80
76
89
69
79

Generate Normal distribution of data in Excel
		Mean	10
		Standard deviation	2
	11.5819814265			Use Copy ...Paste … Paste Special … Values
	8.3738684914				To keep data from continuing to update due to the RAND() feature
	9.0864744902
	9.2572957454
	9.3909571118
	13.7527527534
	10.8007385469
	4.0893192297
	8.9700785395
	9.2928090436
	8.8604097223
	6.4852175137