Instructions

This assignment must be done in Excel. You are expected to create an excel chart with both x bar and r bar charts. If you opt to do all of the calculations by hand then you need to attach a photo of your calculations. You enter the data into excel by hand to create the graph. There is a tutorial that will show you how you can set up these charts and do the calculations by hand.

NO OTHER SOFTWARE WILL BE ACCEPTED. You can hand calculate and use excel to generate the graphs $($attach photos of your calculations$)$ or you can set up your own sheet to do the calculations for you $($I would recommend this, once you have a template set up you can copy it and paste it into other workbooks or worksheets and reuse it$)$

Problem $1)$ For this problem you do not need to create a chart I am only giving you enough information for you to calculate the control limits and calculate the standard deviation for the process.

Samples consisting of $6$ observations each were collected from a process at regular intervals over the course of a production day. The xbar and R values for each sample was calculated. After $50$ samples had been collected, the grand average, x bar bar, was found to equal $40,$ and the average range, Rbar, equaled $4.$ Assume the data is normally distributed.

a$)$ Find the upper and lower control limits for this data.

b$)$ Find the one and two sigma lines for the xbar control chart

c$)$ Find the one and two sigma lines for the R chart.

d$)$ Estimate the mean and standard deviation for this process

Problem $2)$ The data below consists of $20$ samples each consisting of $20$ samples each. $($you should be able to copy and paste this into your spreadsheet$)$

a$)$ Set up the xbar and R charts for this process in excel

b$)$ Is the process in control? Be sure to explain your answer and use complete sentences. Explain why you believe the process is or is not in control. Use the word crazy in your conclusion

Table $1.$

Sample Number x$1$ x$2$ x$3$ x$4$ x$5$

$1138\mathrm{.}1110\mathrm{.}8138\mathrm{.}7137\mathrm{.}4125\mathrm{.}4$

$2149\mathrm{.}3142\mathrm{.}110513492\mathrm{.}3$

$3115\mathrm{.}9135\mathrm{.}6124\mathrm{.}2155117\mathrm{.}4$

$4118\mathrm{.}5116\mathrm{.}5130\mathrm{.}2122\mathrm{.}6100\mathrm{.}2$

$5108\mathrm{.}2123\mathrm{.}8117\mathrm{.}1142\mathrm{.}4150\mathrm{.}9$

$6102\mathrm{.}8112135135145\mathrm{.}8$

$7120\mathrm{.}484\mathrm{.}3112\mathrm{.}8118\mathrm{.}5119\mathrm{.}3$

$8132\mathrm{.}7151\mathrm{.}1124123\mathrm{.}9105\mathrm{.}1$

$9136\mathrm{.}4126\mathrm{.}2154\mathrm{.}7127\mathrm{.}1173\mathrm{.}2$

$10135115\mathrm{.}4149\mathrm{.}1138\mathrm{.}3130\mathrm{.}4$

$11139\mathrm{.}6127\mathrm{.}9151\mathrm{.}1143\mathrm{.}7110\mathrm{.}5$

$12125\mathrm{.}3160\mathrm{.}2130\mathrm{.}4152\mathrm{.}4165\mathrm{.}1$

$13145\mathrm{.}7101\mathrm{.}8149\mathrm{.}5113\mathrm{.}3151\mathrm{.}8$

$14138\mathrm{.}6139131\mathrm{.}9140\mathrm{.}2141\mathrm{.}1$

$15110\mathrm{.}1114\mathrm{.}6165\mathrm{.}1113\mathrm{.}8139\mathrm{.}6$

$16145\mathrm{.}2101154\mathrm{.}6120\mathrm{.}2117\mathrm{.}3$

$17125\mathrm{.}9135\mathrm{.}3121\mathrm{.}5147\mathrm{.}9105$

$18129\mathrm{.}797\mathrm{.}3130\mathrm{.}5109150\mathrm{.}5$

$19123\mathrm{.}4150161\mathrm{.}6148\mathrm{.}4154\mathrm{.}2$

$20144\mathrm{.}8138\mathrm{.}3119\mathrm{.}6151\mathrm{.}8142\mathrm{.}7$

c$)$ Ten additional samples were collected $($See Table $2).$ Using the control limits you established in part a$,$ plot the additional xbar and R values on your original Control Chart $($I only need one chart. add the data to the existing chart you created in part a$.$

d$)$ Is the process in control? Be sure to explain your answer and use complete sentences. Explain why you believe the process is or is not in control.

Table $2$

Sample Number x$1$ x$2$ x$3$ x$4$ x$5$

$1131184\mathrm{.}8182\mathrm{.}2143\mathrm{.}3212\mathrm{.}8$

$2181\mathrm{.}3193\mathrm{.}2180\mathrm{.}7169\mathrm{.}1174\mathrm{.}3$

$3154\mathrm{.}8170\mathrm{.}2168\mathrm{.}4202\mathrm{.}7174\mathrm{.}4$

$4157\mathrm{.}5154\mathrm{.}2169\mathrm{.}1142\mathrm{.}2161\mathrm{.}9$

$5216\mathrm{.}3174\mathrm{.}3166\mathrm{.}2155\mathrm{.}5184\mathrm{.}3$

$6186\mathrm{.}9180\mathrm{.}2149\mathrm{.}2175\mathrm{.}2185$

$7167\mathrm{.}8143\mathrm{.}9157\mathrm{.}5171\mathrm{.}8194\mathrm{.}9$

$8178\mathrm{.}2186\mathrm{.}7142\mathrm{.}4159\mathrm{.}4167\mathrm{.}6$

$9162\mathrm{.}6143\mathrm{.}6132\mathrm{.}8168\mathrm{.}9177\mathrm{.}2$

$10172\mathrm{.}1191\mathrm{.}7203\mathrm{.}4150\mathrm{.}4196\mathrm{.}3$