Problem 7-20
Widetred Tire Co.
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 60000 60000 NORMAL PROBABILITY CALCULATIONS
Std deviation of distribution 2000 2000
Desired x-value 63,250 56,600 Mean of distribution 1020
Calculated z-value 1.63 -1.70 Std Dev of distribution 20
Probability of x, or less 0.94792 0.04457 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 NORM.DIST
52000 -4.00 0.00003 Equivalent Probability Using Desired
52200 -3.90 0.00005 Z Values NORM.DIST x-values
52400 -3.80 0.00007 -4.00 0.00003 940
52600 -3.70 0.00011 -3.60 0.00016 948
52800 -3.60 0.00016 -3.20 0.00069 956
53000 -3.50 0.00023 -2.80 0.00256 964
53200 -3.40 0.00034 -2.40 0.00820 972
53400 -3.30 0.00048 -2.00 0.02275 980
53600 -3.20 0.00069 -1.60 0.05480 988
56800 -1.60 0.05480
57000 -1.50 0.06681
57200 -1.40 0.08076
57400 -1.30 0.09680
57600 -1.20 0.11507
57800 -1.10 0.13567
58000 -1.00 0.15866
58200 -0.90 0.18406
58400 -0.80 0.21186
58600 -0.70 0.24196
58800 -0.60 0.27425
59000 -0.50 0.30854
59200 -0.40 0.34458
59400 -0.30 0.38209
59600 -0.20 0.42074
59800 -0.10 0.46017
60000 0.00 0.50000
60200 0.10 0.53983
60400 0.20 0.57926
60600 0.30 0.61791
60800 0.40 0.65542
61000 0.50 0.69146
61200 0.60 0.72575
61400 0.70 0.75804
61600 0.80 0.78814
61800 0.90 0.81594
62000 1.00 0.84134
62200 1.10 0.86433
62400 1.20 0.88493
62600 1.30 0.90320
62800 1.40 0.91924
63000 1.50 0.93319
63200 1.60 0.94520
63400 1.70 0.95543
63600 1.80 0.96407
63800 1.90 0.97128
64000 2.00 0.97725
64200 2.10 0.98214
64400 2.20 0.98610
64600 2.30 0.98928
64800 2.40 0.99180
65000 2.50 0.99379
65200 2.60 0.99534
65400 2.70 0.99653
65600 2.80 0.99744
65800 2.90 0.99813
66000 3.00 0.99865
66200 3.10 0.99903
66400 3.20 0.99931
66600 3.30 0.99952
66800 3.40 0.99966
67000 3.50 0.99977
67200 3.60 0.99984
67400 3.70 0.99989
67600 3.80 0.99993
67800 3.90 0.99995
68000 4.00 0.99997
0.60
0.70
0.80
0.90
1.00
Cumulative Probability Function
NORM.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
-1.60 0.05480 988
53800 -3.10 0.00097 -1.20 0.11507 996
54000 -3.00 0.00135 -0.80 0.21186 1004
54200 -2.90 0.00187 -0.40 0.34458 1012
54400 -2.80 0.00256 0.00 0.50000 1020
54600 -2.70 0.00347 0.40 0.65542 1028
54800 -2.60 0.00466 0.80 0.78814 1036
55000 -2.50 0.00621 1.20 0.88493 1044
55200 -2.40 0.00820 1.60 0.94520 1052
55400 -2.30 0.01072 2.00 0.97725 1060
55600 -2.20 0.01390 2.40 0.99180 1068
55800 -2.10 0.01786 2.80 0.99744 1076
56000 -2.00 0.02275 3.20 0.99931 1084
56200 -1.90 0.02872 3.60 0.99984 1092
56400 -1.80 0.03593 4.00 0.99997 1100
56600 -1.70 0.04457
-1.20 0.11507 996
Problem 7-20
Widetred Tire Co.
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 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 60000
2000
Probability of X or less 0.50
Calculated X-Value 60000.00
Cumulative
Calculated x-values Probability Reliab= 1-P
52000 0.00003 0.99997
52200 0.00005 0.99995
52400 0.00007 0.99993
54200 0.00187 0.99813
54400 0.00256 0.99744
54600 0.00347 0.99653
54800 0.00466 0.99534
55000 0.00621 0.99379
55200 0.00820 0.99180
55400 0.01072 0.98928
55600 0.01390 0.98610
55800 0.01786 0.98214
58600 0.24196 0.75804
58800 0.27425 0.72575
59000 0.30854 0.69146
59200 0.34458 0.65542
59400 0.38209 0.61791
59600 0.42074 0.57926
59800 0.46017 0.53983
60000 0.50000 0.50000
60200 0.53983 0.46017
60400 0.57926 0.42074
60600 0.61791 0.38209
60800 0.65542 0.34458
61000 0.69146 0.30854
61200 0.72575 0.27425
61400 0.75804 0.24196
61600 0.78814 0.21186
61800 0.81594 0.18406
62000 0.84134 0.15866
62200 0.86433 0.13567
62400 0.88493 0.11507
62600 0.90320 0.09680
62800 0.91924 0.08076
63000 0.93319 0.06681
63200 0.94520 0.05480
63400 0.95543 0.04457
63600 0.96407 0.03593
63800 0.97128 0.02872
64000 0.97725 0.02275
64200 0.98214 0.01786
64400 0.98610 0.01390
66400 0.99931 0.00069
66600 0.99952 0.00048
Std Dev of distribution
0.000
0.700
0.800
0.900
1.000
52000 54000 56000 58000 60000 62000 64000 66000 68000
Tread Life – Miles
X-values vs. Cumulative Probability
66800 0.99966 0.00034
67000 0.99977 0.00023
67200 0.99984 0.00016
Problem 7-20
Widetred Tire 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.05000
Calculated z-value -1.64
Calculated Equivalent
Probability Z Values x-values
0.00003 -4.00 52000
0.00005 -3.90 52200
0.00007 -3.80 52400
0.00011 -3.70 52600
0.00016 -3.60 52800
0.00347 -2.70 54600
0.00466 -2.60 54800
0.00621 -2.50 55000
0.00820 -2.40 55200
0.01072 -2.30 55400
0.01390 -2.20 55600
0.01786 -2.10 55800
0.02275 -2.00 56000
0.02872 -1.90 56200
0.03593 -1.80 56400
0.04457 -1.70 56600
0.05480 -1.60 56800
0.06681 -1.50 57000
0.08076 -1.40 57200
0.09680 -1.30 57400
0.11507 -1.20 57600
0.13567 -1.10 57800
0.15866 -1.00 58000
0.18406 -0.90 58200
0.21186 -0.80 58400
0.24196 -0.70 58600
0.27425 -0.60 58800
0.30854 -0.50 59000
0.34458 -0.40 59200
0.38209 -0.30 59400
0.42074 -0.20 59600
0.46017 -0.10 59800
0.50000 0.00 60000
0.53983 0.10 60200
0.57926 0.20 60400
0.61791 0.30 60600
0.65542 0.40 60800
0.69146 0.50 61000
0.72575 0.60 61200
0.75804 0.70 61400
0.78814 0.80 61600
0.81594 0.90 61800
0.84134 1.00 62000
0.86433 1.10 62200
0.88493 1.20 62400
0.90320 1.30 62600
0.91924 1.40 62800
0.93319 1.50 63000
0.94520 1.60 63200
0.95543 1.70 63400
0.96407 1.80 63600
0.97128 1.90 63800
0.97725 2.00 64000
0.98214 2.10 64200
0.98610 2.20 64400
0.98928 2.30 64600
0.99180 2.40 64800
0.99379 2.50 65000
0.99534 2.60 65200
0.99744 2.80 65600
0.99813 2.90 65800
0.99865 3.00 66000
0.99903 3.10 66200
0.99931 3.20 66400
-5.00
-4.00
-3.00
-2.00
-1.00
5.00
Cumulative
Probability
Z Values vs. Probability
0.00023 -3.50 53000
0.00034 -3.40 53200
0.00048 -3.30 53400
0.00069 -3.20 53600
0.00097 -3.10 53800
0.00135 -3.00 54000
0.00187 -2.90 54200
0.00256 -2.80 54400
0.99952 3.30 66600
0.99966 3.40 66800
0.99977 3.50 67000
0.99989 3.70 67400
0.99993 3.80 67600
0.99995 3.90 67800
0.99997 4.00 68000