Problem 7-11 Instructor Reserve
Airponents, Inc.
Enter data only in the shaded cells
This spreadsheet is designed to calculate the probability of values equal to, or less than, a desired x value,
given the mean and standard deviation of a normally distributed variable. It uses the cumulative normal distribution
Enter the mean of the distribution in shaded cell D8 and the standard deviation in shaded cell D9. below.
Enter the desired X-value in shaded cell D10, below. The calculated z-value and probability will be seen in D11 and D12.
Mean of distribution 40000 40000 NORMAL PROBABILITY CALCULATIONS
Std deviation of distribution 850 850
Calculated z-value 1.20
(X-axis) Probability Using
Prob. of x, or less 0.88493
Desired x-values
Equivalent – Z Values NORMS.DIST
36600 -4.00 0.00003 Equivalent Probability Using Desired
36685 -3.90 0.00005 Z Values NORM.DIST x-values
36770 -3.80 0.00007 -4.00 0.00003 940
36855 -3.70 0.00011 -3.60 0.00016 948
36940 -3.60 0.00016 -3.20 0.00069 956
37025 -3.50 0.00023 -2.80 0.00256 964
37110 -3.40 0.00034 -2.40 0.00820 972
37195 -3.30 0.00048 -2.00 0.02275 980
37280 -3.20 0.00069 -1.60 0.05480 988
37365 -3.10 0.00097 -1.20 0.11507 996
38385 -1.90 0.02872 3.60 0.99984 1092
38470 -1.80 0.03593 4.00 0.99997 1100
38555 -1.70 0.04457
38640 -1.60 0.05480
38725 -1.50 0.06681 <– x = 38750
38810 -1.40 0.08076
38895 -1.30 0.09680
38980 -1.20 0.11507
39065 -1.10 0.13567
39150 -1.00 0.15866
39235 -0.90 0.18406
39320 -0.80 0.21186
39405 -0.70 0.24196
39490 -0.60 0.27425
39575 -0.50 0.30854
39660 -0.40 0.34458
39745 -0.30 0.38209
40680 0.80 0.78814
40765 0.90 0.81594
40850 1.00 0.84134
40935 1.10 0.86433
41020 1.20 0.88493
41105 1.30 0.90320
41190 1.40 0.91924
41615 1.90 0.97128
41700 2.00 0.97725
41785 2.10 0.98214
41870 2.20 0.98610
41955 2.30 0.98928
42040 2.40 0.99180
42125 2.50 0.99379
42210 2.60 0.99534
42295 2.70 0.99653
42380 2.80 0.99744
42465 2.90 0.99813
42550 3.00 0.99865
0.50
0.60
0.70
0.80
0.90
1.00
Cumulative Probability Function
NORMS.DIST
Calculated z-value 1.20
Prob. of x, or less 0.88493
Equivalent
Probability Using
Desired
Z Values NORM.DIST x-values
–4.00 0.00003 940
–3.60 0.00016 948
–3.20 0.00069 956
–2.80 0.00256 964
–2.40 0.00820 972
–2.00 0.02275 980
–1.60 0.05480 988
–1.20 0.11507 996
3.60 0.99984 1092
4.00 0.99997 1100
Desired x-value 41490 38750 Mean of distribution 1020
42635 3.10 0.99903
42720 3.20 0.99931
42805 3.30 0.99952
42890 3.40 0.99966
42975 3.50 0.99977
43060 3.60 0.99984
43145 3.70 0.99989
43230 3.80 0.99993
43315 3.90 0.99995
43400 4.00 0.99997
Problem 7-11 Instructor Reserve
Airponents, Inc.
X-value Calculations Given Probabilities Using the Inverse Normal Distribution
This spreadsheet is designed to calculate the X-value based on probability of values equal to, or less than a desired x value,
of a normally distributed variable. It requires input of a known mean and standard deviation and uses the inverse of the cumulative normal distribution
Enter the mean of the distribution in cell D8 and the standard deviation in cell D9, below.
Enter the desired probability in cell D10, and the calculated x-value will be seen in D11.
Mean of distribution 40000
x-values Probability
Calculated x-values
Probability Reliability – 1-P 37450 0.00135
36600 0.00003 0.99997 38300 0.02275
36685 0.00005 0.99995 39150 0.15866
36770 0.00007 0.99993 40000 0.50000
36855 0.00011 0.99989 40850 0.84134
36940 0.00016 0.99984 40850 0.84134
37025 0.00023 0.99977 42550 0.99865
37110 0.00034 0.99966 43400 0.99997
37195 0.00048 0.99952
37790 0.00466 0.99534
37875 0.00621 0.99379
37960 0.00820 0.99180
38045 0.01072 0.98928
38130 0.01390 0.98610
38215 0.01786 0.98214
38300 0.02275 0.97725
38385 0.02872 0.97128
38470 0.03593 0.96407
38555 0.04457 0.95543
38640 0.05480 0.94520
38725 0.06681 0.93319
39490 0.27425 0.72575
39575 0.30854 0.69146
39660 0.34458 0.65542
39745 0.38209 0.61791
39830 0.42074 0.57926
39915 0.46017 0.53983
0.300
0.400
0.500
0.600
0.700
0.800
0.900
1.000
Probability
X-values vs. Cumulative Probability
Cumulative Probability
0.500
0.600
0.700
0.800
0.900
1.000
Reliability Curve
40000 0.50000 0.50000
40085 0.53983 0.46017
40170 0.57926 0.42074
40255 0.61791 0.38209
40340 0.65542 0.34458
40425 0.69146 0.30854
40510 0.72575 0.27425
40595 0.75804 0.24196
40680 0.78814 0.21186
40765 0.81594 0.18406
40850 0.84134 0.15866
40935 0.86433 0.13567
41020 0.88493 0.11507
41105 0.90320 0.09680
41190 0.91924 0.08076
41955 0.98928 0.01072
42040 0.99180 0.00820
42125 0.99379 0.00621
42210 0.99534 0.00466
42295 0.99653 0.00347
42380 0.99744 0.00256
42465 0.99813 0.00187
42550 0.99865 0.00135
42635 0.99903 0.00097
42720 0.99931 0.00069
42805 0.99952 0.00048
42890 0.99966 0.00034
42975 0.99977 0.00023
43060 0.99984 0.00016
43145 0.99989 0.00011
43230 0.99993 0.00007
43315 0.99995 0.00005
43400 0.99997 0.00003
Problem 7-11 Instructor Reserve
Airponents, Inc.
Z and X-value Calculations Given Probabilites, Using the Inverse Normal Distribution – Template
This spreadsheet is designed to calculate the z-value based on probability of values equal to, or less than,
an equivalent x-value of a normally distributed variable. It uses the inverse of the cumulative normal distribution.
Enter the desired probability of the Z-value or less in the shaded cell D8, below.
The calculated z-value will be seen in cell D9.
Probability of x-value, or less 0.03000
Calculated Equivalent
Probability Z Values x-values
0.00003 -4.00 36600
0.00005 -3.90 36685
0.00007 -3.80 36770
0.00011 -3.70 36855
0.00016 -3.60 36940
0.00023 -3.50 37025
0.00034 -3.40 37110
0.00048 -3.30 37195
0.00069 -3.20 37280
0.01072 -2.30 38045
0.01390 -2.20 38130
0.01786 -2.10 38215
0.02275 -2.00 38300
0.02872 -1.90 38385
0.03593 -1.80 38470
0.04457 -1.70 38555
0.05480 -1.60 38640
0.06681 -1.50 38725
0.08076 -1.40 38810
0.09680 -1.30 38895
0.11507 -1.20 38980
0.13567 -1.10 39065
0.30854 -0.50 39575
0.34458 -0.40 39660
-5.00
-4.00
2.00
3.00
4.00
5.00
Z Values vs. Probability
Z Values
0.38209 -0.30 39745
0.42074 -0.20 39830
0.46017 -0.10 39915
0.50000 0.00 40000
0.53983 0.10 40085
0.57926 0.20 40170
0.61791 0.30 40255
0.65542 0.40 40340
0.69146 0.50 40425
0.72575 0.60 40510
0.96407 1.80 41530
0.97128 1.90 41615
0.97725 2.00 41700
0.98214 2.10 41785
0.98610 2.20 41870
0.98928 2.30 41955
0.99180 2.40 42040
0.99379 2.50 42125
0.99534 2.60 42210
0.99653 2.70 42295
0.99744 2.80 42380
0.99813 2.90 42465
0.99865 3.00 42550
0.99903 3.10 42635
0.99931 3.20 42720
0.99952 3.30 42805
0.99966 3.40 42890
0.99977 3.50 42975
0.99984 3.60 43060
0.99989 3.70 43145
0.99993 3.80 43230
0.99995 3.90 43315
0.99997 4.00 43400