Problem 6-4 Fruitayuda, Inc
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 11.7 NORMAL PROBABILITY CALCULATIONS
Std deviation of distribution 0.18
Desired x-value 12 Mean of distribution 1020
Calculated z-value 1.67 Std Dev of distribution 20
Probability of x, or less 0.95221 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
9.7 -11.11 0.00000 Equivalent Probability Using Desired
9.75 -10.83 0.00000 Z Values NORM.DIST x-values
9.8 -10.56 0.00000 -4.00 0.00003 940
9.85 -10.28 0.00000 -3.60 0.00016 948
9.9 -10.00 0.00000 -3.20 0.00069 956
9.95 -9.72 0.00000 -2.80 0.00256 964
10 -9.44 0.00000 -2.40 0.00820 972
10.05 -9.17 0.00000 -2.00 0.02275 980
11.3 -2.22 0.01313
11.35 -1.94 0.02592
11.4 -1.67 0.04779
11.45 -1.39 0.08243
11.5 -1.11 0.13326
11.55 -0.83 0.20233
11.6 -0.56 0.28926
11.65 -0.28 0.39059
11.7 0.00 0.50000
11.75 0.28 0.60941
11.8 0.56 0.71074
11.85 0.83 0.79767
11.9 1.11 0.86674
11.95 1.39 0.91757
12 1.67 0.95221
12.05 1.94 0.97408
12.1 2.22 0.98687
12.15 2.50 0.99379
12.2 2.78 0.99726
12.25 3.06 0.99888
12.3 3.33 0.99957
12.35 3.61 0.99985
12.4 3.89 0.99995
12.45 4.17 0.99998
12.5 4.44 1.00000
12.55 4.72 1.00000
12.6 5.00 1.00000
12.65 5.28 1.00000
12.7 5.56 1.00000
12.75 5.83 1.00000
12.8 6.11 1.00000
12.85 6.39 1.00000
12.9 6.67 1.00000
12.95 6.94 1.00000
13 7.22 1.00000
13.05 7.50 1.00000
13.1 7.78 1.00000
13.15 8.06 1.00000
13.2 8.33 1.00000
13.25 8.61 1.00000
13.3 8.89 1.00000
13.35 9.17 1.00000
13.4 9.44 1.00000
13.45 9.72 1.00000
13.5 10.00 1.00000
13.55 10.28 1.00000
13.6 10.56 1.00000
13.65 10.83 1.00000
13.7 11.11 1.00000
0.60
0.70
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
-2.40 0.00820 972
-2.00 0.02275 980
10.1 -8.89 0.00000 -1.60 0.05480 988
10.15 -8.61 0.00000 -1.20 0.11507 996
10.85 -4.72 0.00000
10.9 -4.44 0.00000
10.95 -4.17 0.00002
11 -3.89 0.00005
11.05 -3.61 0.00015
11.1 -3.33 0.00043
11.15 -3.06 0.00112
11.2 -2.78 0.00274
11.25 -2.50 0.00621
-1.60 0.05480 988
-1.20 0.11507 996
Problem 6-4 Fruitayuda, Inc
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 11.7
Std Dev of distribution 0.18
Probability of X or less 0.95221
Calculated X-Value 12.00 Calculated Given
x-values Probability
Calculated x-values Probability 11.16 0.00135
10.98 0.00003 11.34 0.02275
10.998 0.00005 11.52 0.15866
11.016 0.00007 11.7 0.50000
11.034 0.00011 11.88 0.84134
11.052 0.00016 11.88 0.84134
11.394 0.04457 11.16 0.00135
11.412 0.05480 11.34 0.02275
11.43 0.06681 11.52 0.15866
11.448 0.08076 11.7 0.50000
11.466 0.09680 11.88 0.84134
11.484 0.11507 12.06 0.97725
11.502 0.13567 12.24 0.99865
11.52 0.15866 12.42 0.99997
11.538 0.18406
11.556 0.21186
11.574 0.24196
11.592 0.27425
11.61 0.30854
11.628 0.34458
11.646 0.38209
11.664 0.42074
11.682 0.46017
11.7 0.50000
11.718 0.53983
11.736 0.57926
11.754 0.61791
11.772 0.65542
11.79 0.69146
11.808 0.72575
11.826 0.75804
11.844 0.78814
11.862 0.81594
11.88 0.84134
11.898 0.86433
11.916 0.88493
11.934 0.90320
11.952 0.91924
11.97 0.93319
11.988 0.94520
0.600
0.700
0.800
0.900
1.000
X-values vs. Cumulative Probability
Problem 6-5 Fruitayuda, Inc
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.95221
1.67
Probability of x-value, or less
Calculated z-value Calculated Equivalent
Probability Z Values x-values
0.00003 -4.00 10.98
0.00005 -3.90 10.998
0.00007 -3.80 11.016
0.03593 -1.80 11.376
0.04457 -1.70 11.394
0.05480 -1.60 11.412
0.06681 -1.50 11.43
0.08076 -1.40 11.448
0.09680 -1.30 11.466
0.11507 -1.20 11.484
0.13567 -1.10 11.502
0.15866 -1.00 11.52
0.18406 -0.90 11.538
0.21186 -0.80 11.556
0.24196 -0.70 11.574
0.27425 -0.60 11.592
0.30854 -0.50 11.61
0.34458 -0.40 11.628
0.38209 -0.30 11.646
0.42074 -0.20 11.664
0.46017 -0.10 11.682
0.50000 0.00 11.7
0.53983 0.10 11.718
0.57926 0.20 11.736
0.61791 0.30 11.754
0.65542 0.40 11.772
0.69146 0.50 11.79
0.72575 0.60 11.808
0.75804 0.70 11.826
0.78814 0.80 11.844
0.81594 0.90 11.862
0.84134 1.00 11.88
0.86433 1.10 11.898
0.88493 1.20 11.916
0.90320 1.30 11.934
0.91924 1.40 11.952
0.93319 1.50 11.97
0.94520 1.60 11.988
0.95543 1.70 12.006
0.96407 1.80 12.024
0.97128 1.90 12.042
0.97725 2.00 12.06
0.98214 2.10 12.078
0.98610 2.20 12.096
0.98928 2.30 12.114
0.99180 2.40 12.132
0.99379 2.50 12.15
0.99534 2.60 12.168
0.99653 2.70 12.186
0.99744 2.80 12.204
0.99865 3.00 12.24
0.99903 3.10 12.258
0.99931 3.20 12.276
0.99952 3.30 12.294
0.99966 3.40 12.312
5.00
Z Values vs. Probability
0.00011 -3.70 11.034
0.00016 -3.60 11.052
0.00023 -3.50 11.07
0.00034 -3.40 11.088
0.00048 -3.30 11.106
0.00069 -3.20 11.124
0.00097 -3.10 11.142
0.00135 -3.00 11.16
0.00187 -2.90 11.178
0.00256 -2.80 11.196
0.00347 -2.70 11.214
0.00466 -2.60 11.232
0.00621 -2.50 11.25
0.00820 -2.40 11.268
0.01072 -2.30 11.286
0.01390 -2.20 11.304
0.01786 -2.10 11.322
0.02275 -2.00 11.34
0.02872 -1.90 11.358
0.99977 3.50 12.33
0.99984 3.60 12.348
0.99989 3.70 12.366
0.99995 3.90 12.402
0.99997 4.00 12.42