Question

USING MICROSOFT EXCEL PROGRAM

Once you have built the Excel tables, with all the changes in different tables,and answered all the questions you have to send the work (Excel sheets and answered questions) to the professor using the Assignment link in Black Board. To use this link enter the Course Content in Black Board, select the Assignment Module 4, attach the file and submit. You will have only one (1) attempt to send this activity and has a value of 10 points. The deadline for complete this assignment is indicated on "Tools", which is located in "Calendar" in the "Blackboard" platform. Solve the following problem and compute the three measures of variability (variance, standard deviation, and range. Also construct a Normal Probability Plot of problem 6:36 of the textbook, page 203. In their book Introduction to Linear Regression Analysis (4ih. Edition, Wiley, 206), Montgomery,Peck, and Vinning presented measurements on NbOCl3 concentration from a tube-flow reactor experiment. The data, in gram-mole per liter x 103, are as follows. Compute the sample mean, the sample variance, the sample standard deviation, the range and the Normal Probability Plot 450 450 473 507 457 452 453 1215 1256 1145 1085 1066 1111 1364 1254 1396 1575 1617 1733 2753 3186 3227 34691911 2588 2635 2725 What is the sample mean? (1539) 1. (917030.8462) 2. What is the sample variance? What is the sample range? (3019) 3. Construct the Normal Probability Plot 4.

Answer 1

Calculating the metrics in Excel:

1) Sample mean = Sum of data / No. of data points = 1539

2) Sample variance = Var(Xi) = 917030

3) Max = 3469, Min = 450

=> Range = Max - Min = 3019

4) Normal probability can be made in Excel using the following steps:

a) Sort the data in ascending order

b) Number the sorted data from 1 to n (n= sample size)

c) Calculate the cumulative probability for each data point, where cumulative probability = (i - 0.5)/n. Note that 450 appears twice in the sample.

d) Calculate the Z-score for each cum. probability value using NORMSINV() function

We get the following table:

Data	Sorted	Index	Cum. Prob.	Z-score
450	450	1	0.01851852	-2.08535557
450	450	2	0.05555556	-1.59321882
473	452	3	0.09259259	-1.32495769
507	453	4	0.12962963	-1.12814365
457	457	5	0.16666667	-0.96742157
452	473	6	0.2037037	-0.82846465
453	507	7	0.24074074	-0.70392179
1215	1066	8	0.27777778	-0.5894558
1256	1085	9	0.31481481	-0.48224821
1145	1111	10	0.35185185	-0.38032564
1085	1145	11	0.38888889	-0.28221615
1066	1215	12	0.42592593	-0.18675612
1111	1254	13	0.46296296	-0.09297185
1364	1256	14	0.5	0
1254	1364	15	0.53703704	0.09297185
1396	1396	16	0.57407407	0.18675612
1575	1575	17	0.61111111	0.28221615
1617	1617	18	0.64814815	0.38032564
1733	1733	19	0.68518519	0.48224821
2753	1911	20	0.72222222	0.5894558
3186	2588	21	0.75925926	0.70392179
3227	2635	22	0.7962963	0.82846465
3469	2725	23	0.83333333	0.96742157
1911	2753	24	0.87037037	1.12814365
2588	3186	25	0.90740741	1.32495769
2635	3227	26	0.94444444	1.59321882
2725	3469	27	0.98148148	2.08535557

Now, plotting the sorted data vs the Z-scores we get the following Normal Probability Plot:

Normal Probability Plot 2.5 2 1.5 1 0.5 0 500 100 1500 2000 2500 3000 3500 4000 N-0.5 1 -1.5 -2 -2.5 Sorted Data o n