Question

how does kne do this in excel . how do you find the ucl lcl and cl? how do ufijd the values for the ucl lcl amd cl.

show me an example in exel and show me the equations used

viid uiat Cume 1. Simulate 4 observations of a normal distribution with mean 3.5 and standard deviation of 1.7078. In another cell, get the average on these 4 observations and define it as your Crystal ball prediction. put 1000 as number of trials and run it. Now, you should have a histogram for the average of 4 observations. 2. Create your control chart limits (UCL,CL, LCL) at 3 sigma-level. (note: having the values is enough, no control chart is needed). 3. Using the histogram in 1, Find the probability of the average of 4 die rolls being outside the control limits Submit your histogram create in number 1, your calculations in number 2, and the probability requested in number 3.

Answer 1

To do the simulation in excel first go to File -> Options -> Add-Ins -> select Analysis ToolPak -> Click on 'Go' -> Again check Analysis ToolPak -> Click on 'Ok'.

Then go to Data -> Click on 'Data Analysis' from the extreme right -> Select 'Random Number Generation' -> Click 'Ok'.

Then a dialogue box will be opened. There, insert 4 as the Number of variables and 1000 the number of random numbers for each of the 4 variables. Choose 'Normal' in the dropdown list for Distribution. Enter mean as 3.5 as standard deviation 1.7078. Select the output range that is, the area in the Excel worksheet where you want the output to appear. Then click 'OK'.

Now in the 4 columns, you will have the data. Now, for each of the 1000 rows take mean of 4 columns and insert it into the 5^th column. Now, this fifth column contains 1000 averages of 4 observations.

Step 3: Enter your BINS into a single column (for example, column B) (What is a BIN in statistics?). In Excel, you'll want to enter the upper boundary for the BINS in that column. For example, if you have 0-10, 11-20 and 21-30 you would enter 10,20,30. Step 4: Click the "Data" tab, then click the "Data Analysis" button. Step 5: Highlight "Histogram and then click "OK. Step 6: Type the location of your data into the Input Range Box. For example, if your data is in cells A2 to A10 then type A2:A10 into the box Step 7: Type the location of your BINS into the "Bin Range" box. For example, type "B2:B4" into the box to indicate your BINS are in cells B2 to B4. Step 8: Click an option for where you want your data to appear. For example, click the "New Worksheet button. Step 9: Check the "Chart Output" box. If you don't check this, you'll only get a frequency chart (a list), not the actual

Then click "OK" and the histogram will be obtained.

1. The histogram obtained after the simulation study is as follows -

Histogram 60 50 40 30 Frequency 20 10 Nun co Ln Bin Frequency 0.5才 0.8 1.1 - 1.4 3 1.7キ 97 t 9's 5.9 6.5

2. tet X, x2, X3, X4 be the vandom nample from N3.5, .07 oulation detne x Let us Then ~N3.5 70 wsing 3-Aigma eve the control chart imits ii ae, Then UCし= 3.5+3 1.7o78 ろ.5 LCL3.5- 3 O.9383 to kno that hiotogram ot 3, From the 10v0 averages of oloaervations oniy and aumong the UcL than average s movę LCL and aerage i less than 6. 0o17 hintogram theptptorlity . Hence, from the tthe aveaze iying outaide the condro 2. Rinnit is (Ans