Problem 6-8 Piedra Cretebuilders
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.1217
Desired x-value 15.75 Mean of distribution 1020
Calculated z-value 2.054 Std Dev of distribution 20
Probability of x, or less 0.98002 Desired x-value 1044
Calculated z-value 1.20
(X-axis) Probability Using Prob. of x, or less 0.88493
Desired x-values Equivalent – Z Values NORMS.DIST
15.030 -3.86 0.00006 Equivalent Probability Using Desired
15.048 -3.71 0.00010 Z Values NORM.DIST x-values
15.066 -3.57 0.00018 -4.00 0.00003 940
15.084 -3.42 0.00032 -3.60 0.00016 948
15.102 -3.27 0.00054 -3.20 0.00069 956
15.120 -3.12 0.00090 -2.80 0.00256 964
15.408 -0.76 0.22484 3.60 0.99984 1092
15.426 -0.61 0.27158 4.00 0.99997 1100
15.444 -0.46 0.32271
15.462 -0.31 0.37743
15.480 -0.16 0.43473
15.498 -0.02 0.49344
15.516 0.13 0.55230
15.534 0.28 0.61002
15.552 0.43 0.66541
15.570 0.58 0.71742
15.588 0.72 0.76519
15.606 0.87 0.80812
15.624 1.02 0.84587
15.642 1.17 0.87836
15.660 1.31 0.90570
15.678 1.46 0.92821
15.696 1.61 0.94636
15.714 1.76 0.96066
15.732 1.91 0.97170
15.750 2.05 0.98002
15.768 2.20 0.98617
15.786 2.35 0.99061
15.804 2.50 0.99375
15.822 2.65 0.99593
15.840 2.79 0.99740
15.858 2.94 0.99837
15.876 3.09 0.99900
15.894 3.24 0.99940
15.912 3.39 0.99964
15.930 3.53 0.99979
15.948 3.68 0.99988
15.966 3.83 0.99994
15.984 3.98 0.99997
16.002 4.12 0.99998
16.020 4.27 0.99999
16.038 4.42 1.00000
16.056 4.57 1.00000
16.074 4.72 1.00000
16.092 4.86 1.00000
16.110 5.01 1.00000
16.128 5.16 1.00000
16.146 5.31 1.00000
16.164 5.46 1.00000
16.182 5.60 1.00000
16.200 5.75 1.00000
16.218 5.90 1.00000
16.236 6.05 1.00000
16.254 6.20 1.00000
16.272 6.34 1.00000
16.290 6.49 1.00000
16.308 6.64 1.00000
16.326 6.79 1.00000
16.344 6.94 1.00000
16.362 7.08 1.00000
16.380 7.23 1.00000
16.398 7.38 1.00000
16.416 7.53 1.00000
16.434 7.67 1.00000
16.452 7.82 1.00000
16.470 7.97 1.00000
0.80
0.90
1.00
Cumulative Probability Function
NORMS.DIST
Mean of distribution 1020
Std Dev of distribution 20
Desired x-value 1044
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
3.60 0.99984 1092
4.00 0.99997 1100
15.138 -2.97 0.00147 -2.40 0.00820 972
15.156 -2.83 0.00235 -2.00 0.02275 980
15.174 -2.68 0.00370 -1.60 0.05480 988
15.192 -2.53 0.00569 -1.20 0.11507 996
15.210 -2.38 0.00859 -0.80 0.21186 1004
15.228 -2.24 0.01271 -0.40 0.34458 1012
15.246 -2.09 0.01844 0.00 0.50000 1020
15.264 -1.94 0.02624 0.40 0.65542 1028
15.282 -1.79 0.03662 0.80 0.78814 1036
15.300 -1.64 0.05015 1.20 0.88493 1044
15.318 -1.50 0.06739 1.60 0.94520 1052
15.336 -1.35 0.08890 2.00 0.97725 1060
15.354 -1.20 0.11513 2.40 0.99180 1068
15.372 -1.05 0.14645 2.80 0.99744 1076
15.390 -0.90 0.18303 3.20 0.99931 1084
-2.40 0.00820 972
-2.00 0.02275 980
-1.60 0.05480 988
-1.20 0.11507 996
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.40 0.99180 1068
2.80 0.99744 1076
Problem 6-8 Piedra Cretebuilders
X-value Calculations Given Probabilities Using the Inverse Normal Distribution – Template
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 D9 and the standard deviation in cell D10, below. Enter the desired probability in cell D11, and the calculated x-value will be seen in D12.
Mean of distribution 15.5
Std Dev of distribution 0.1217
Probability of X or less 0.98
Calculated X-Value 15.75 Calculated Given
x-values Probability
Calculated x-values Probability 15.1349 0.00135
15.22009 0.01072
15.23226 0.01390
15.24443 0.01786
15.2566 0.02275 Truncated
15.26877 0.02872 x-values Probability
15.42698 0.27425
15.43915 0.30854
15.45132 0.34458
15.46349 0.38209
15.47566 0.42074
15.48783 0.46017
15.5 0.50000
15.51217 0.53983
15.52434 0.57926
15.53651 0.61791
15.54868 0.65542
15.56085 0.69146
15.57302 0.72575
15.58519 0.75804
15.59736 0.78814
15.60953 0.81594
15.6217 0.84134
15.63387 0.86433
15.64604 0.88493
15.65821 0.90320
15.67038 0.91924
15.68255 0.93319
15.69472 0.94520
15.70689 0.95543
15.71906 0.96407
15.88944 0.99931
15.90161 0.99952
15.91378 0.99966
15.92595 0.99977
15.93812 0.99984
15.95029 0.99989
15.96246 0.99993
15.97463 0.99995
15.9868 0.99997
1.000
X-values vs. Cumulative Probability
Problem 6-8 Piedra Cretebuilders
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 D9, below. The calculated z-value will be seen in cell D10.
0.98000
2.054
Probability of x-value, or less
Calculated z-value Calculated Equivalent
Probability Z Values x-values
0.00003 -4.00 15.0132
0.00005 -3.90 15.0254
0.00007 -3.80 15.0375
0.00011 -3.70 15.0497
0.00016 -3.60 15.0619
0.00023 -3.50 15.0741
0.00034 -3.40 15.0862
0.00048 -3.30 15.0984
0.00069 -3.20 15.1106
0.00097 -3.10 15.1227
0.02275 -2.00 15.2566
0.02872 -1.90 15.2688
0.03593 -1.80 15.2809
0.04457 -1.70 15.2931
0.05480 -1.60 15.3053
0.06681 -1.50 15.3175
0.08076 -1.40 15.3296
0.09680 -1.30 15.3418
0.11507 -1.20 15.3540
0.13567 -1.10 15.3661
0.15866 -1.00 15.3783
0.18406 -0.90 15.3905
0.21186 -0.80 15.4026
0.24196 -0.70 15.4148
0.27425 -0.60 15.4270
0.30854 -0.50 15.4392
0.34458 -0.40 15.4513
0.38209 -0.30 15.4635
0.42074 -0.20 15.4757
0.46017 -0.10 15.4878
0.50000 0.00 15.5000
0.53983 0.10 15.5122
0.57926 0.20 15.5243
0.61791 0.30 15.5365
0.65542 0.40 15.5487
0.69146 0.50 15.5609
0.72575 0.60 15.5730
0.75804 0.70 15.5852
0.78814 0.80 15.5974
0.81594 0.90 15.6095
0.84134 1.00 15.6217
0.86433 1.10 15.6339
0.88493 1.20 15.6460
0.90320 1.30 15.6582
0.91924 1.40 15.6704
0.93319 1.50 15.6826
0.94520 1.60 15.6947
0.95543 1.70 15.7069
0.96407 1.80 15.7191
0.97128 1.90 15.7312
0.97725 2.00 15.7434
0.98214 2.10 15.7556
0.98610 2.20 15.7677
0.98928 2.30 15.7799
0.99180 2.40 15.7921
0.99379 2.50 15.8043
0.99534 2.60 15.8164
0.99653 2.70 15.8286
0.99744 2.80 15.8408
0.99865 3.00 15.8651
0.99903 3.10 15.8773
0.99931 3.20 15.8894
0.99952 3.30 15.9016
0.99966 3.40 15.9138
-5.00
2.00
3.00
4.00
5.00
Z Values vs. Probability
Z Values
0.00135 -3.00 15.1349
0.00187 -2.90 15.1471
0.00256 -2.80 15.1592
0.00347 -2.70 15.1714
0.00466 -2.60 15.1836
0.00621 -2.50 15.1958
0.00820 -2.40 15.2079
0.01072 -2.30 15.2201
0.01390 -2.20 15.2323
0.01786 -2.10 15.2444