Problem 6 – Instructor Reserve
Piedra Salt Co.
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.99000
Calculated z-value 2.326
Calculated
Equivalent
Probability Z Values x-values
0.00003 -4.00 14.640
0.00005 -3.90 14.662
0.00007 -3.80 14.683
0.00011 -3.70 14.705
0.00016 -3.60 14.726
0.00023 -3.50 14.748
0.00034 -3.40 14.769
0.00048 -3.30 14.791
0.01786 -2.10 15.049
0.02275 -2.00 15.070
0.02872 -1.90 15.092
0.03593 -1.80 15.113
0.04457 -1.70 15.135
0.05480 -1.60 15.156
0.06681 -1.50 15.178
0.08076 -1.40 15.199
0.09680 -1.30 15.221
0.11507 -1.20 15.242
0.13567 -1.10 15.264
0.15866 -1.00 15.285
0.18406 -0.90 15.307
0.21186 -0.80 15.328
0.24196 -0.70 15.350
0.27425 -0.60 15.371
0.69146 0.50 15.608
0.72575 0.60 15.629
0.75804 0.70 15.651
0.78814 0.80 15.672
0.81594 0.90 15.694
0.84134 1.00 15.715
0.86433 1.10 15.737
0.88493 1.20 15.758
0.90320 1.30 15.780
0.91924 1.40 15.801
0.93319 1.50 15.823
0.94520 1.60 15.844
0.99744 2.80 16.102
0.99813 2.90 16.124
0.99865 3.00 16.145
0.99903 3.10 16.167
-5.00
-4.00
3.00
4.00
5.00
Z Values vs. Probability
Z Values
0.99931 3.20 16.188
0.99952 3.30 16.210
0.99966 3.40 16.231
0.99977 3.50 16.253
0.99984 3.60 16.274
0.99989 3.70 16.296
0.99993 3.80 16.317
0.99995 3.90 16.339
0.99997 4.00 16.360
Problem 6 – Check Solution
Piedra Salt Co.
NOTE: This calculation is only possible after solving for the process standard deviation for the problem,
It allows us to check whether the calculated z-value, corresponding to the desired x=16, is correct, given that we will only have a 2% probability of overflow.
Probability Calculations Using the Normal Distribution – Template
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 15.5 NORMAL PROBABILITY CALCULATIONS
Std deviation of distribution 0.215
Calculated z-value 1.20
(X-axis) Probability Using
Prob. of x, or less 0.88493
Desired x-values
NORMS.DIST
14.640 -4.00 0.00003 Equivalent Probability Using Desired
14.662 -3.90 0.00005 Z Values NORM.DIST x-values
14.683 -3.80 0.00007 -4.00 0.00003 940
14.705 -3.70 0.00011 -3.60 0.00016 948
14.726 -3.60 0.00016 -3.20 0.00069 956
14.898 -2.80 0.00256 0.00 0.50000 1020
14.920 -2.70 0.00347 0.40 0.65542 1028
14.941 -2.60 0.00466 0.80 0.78814 1036
14.963 -2.50 0.00621 1.20 0.88493 1044
14.984 -2.40 0.00820 1.60 0.94520 1052
15.006 -2.30 0.01072 2.00 0.97725 1060
15.027 -2.20 0.01390 2.40 0.99180 1068
15.328 -0.80 0.21186
15.350 -0.70 0.24196
15.371 -0.60 0.27425
15.393 -0.50 0.30854
15.414 -0.40 0.34458
15.436 -0.30 0.38209
15.457 -0.20 0.42074
15.479 -0.10 0.46017
15.500 0.00 0.50000
15.522 0.10 0.53983
15.543 0.20 0.57926
15.780 1.30 0.90320
15.801 1.40 0.91924
15.823 1.50 0.93319
15.844 1.60 0.94520
15.866 1.70 0.95543
15.887 1.80 0.96407
15.909 1.90 0.97128
15.930 2.00 0.97725
15.952 2.10 0.98214
15.973 2.20 0.98610
15.995 2.30 0.98928
16.016 2.40 0.99180
16.167 3.10 0.99903
16.188 3.20 0.99931
16.210 3.30 0.99952
0.00
0.10
0.20
0.30
0.80
0.90
1.00
-4.0-3.8-3.6-3.4-3.2-3.0-2.8-2.6-2.4-2.2-2.0-1.8-1.6-1.4-1.2-1.0-0.8-0.6-0.4-0.2 0.0 0.2 0.4 0.6 0.8 1.0 1.2 1.4 1.6 1.8 2.0 2.2 2.4 2.6 2.8 3.0 3.2 3.4 3.6 3.8 4.0
Cumulative Probability
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
0.00 0.50000 1020
0.40 0.65542 1028
0.80 0.78814 1036
1.20 0.88493 1044
1.60 0.94520 1052
2.00 0.97725 1060
Desired x-value 16 Mean of distribution 1020
16.231 3.40 0.99966
16.253 3.50 0.99977
16.274 3.60 0.99984
16.296 3.70 0.99989
16.317 3.80 0.99993
16.339 3.90 0.99995
16.360 4.00 0.99997