Math Help in Excel

Instructor: These cells contain some hints, tips, and self-checks. If you would like to print any of the tips, right click the cell containing the tip and select Edit Comment. Highlight the text, copy and paste in a any text document like WORD for printing.

Instructor: EXCEL 2003 or earlier Click on the TOOLS tab in the top bar of Excel. If your computer already has the “Data Analysis” option listed, you are ready to go. Your Data Analysis tools have already been added. Under the Data Analysis Function you will find some of the more advanced functions that we will be discussing such as ANOVAs, and t tests. If you do not see Data Analysis listed under the TOOLS tab, I encourage you to go to the Help function in Excel for specific instructions on how to load “the Data Analysis Toolpak” in your version of Excel. Here are the easy standard instructions for Excel 2003 if you need to load the Add-Ins. 1) On the Tools menu, click Add-Ins. 2) In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK. 3) When you load the Analysis Toolpak, the DATA ANALYSIS command is automatically added to the TOOLS menu. If your version if slightly different than the above, refer to your HELP function for details on loading Tookpak. See the next spreadsheet at the bottom of this worksheet entitled EXCEL EXAMPLES for an illustration. The Data Analysis function in Excel is NOT required for the completion of this course.

Villanova instructor: Excel 2007 The Analysis ToolPak is a Microsoft Office Excel add-in program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first. 1. Click the Microsoft Office Button , and then click Excel Options. 2. Click Add-Ins, and then in the Manage box, select Excel Add-ins. 3. Click Go. 4. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK. If you get prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it. 5. After you load the Analysis ToolPak, the Data Analysis command is available in the ANALYSIS group on the DATA tab. See the next spreadsheet EXCEL EXAMPLES for an illustration. The Data Analysis function in Excel is NOT required for this course.

Instructor: It is beyond the scope of this Black Belt course to teach students how to perform all of the functions of Excel. The Microsoft web site has excellent FREE tutorials for both Excel 2003 or Excel 2007. Excel 2007 http://office.microsoft.com/en-us/training/CR100479681033.aspx Excel 2003 http://office.microsoft.com/en-us/training/CR061831141033.aspx Or, ask a business colleague, who is proficient in Excel to show you how to do some of the basic things such as copying, cutting, pasting, copying from sheet to sheet, etc. Please do not expect your Black Belt class instructor to teach you how to use Excel software. That is not the objective of this course. This course will highlight specific Excel commands that are unique to Six Sigma, but it is not the objective of this course to teach basic Excel commands. Please use the suggested resources listed above.

Scatter Diagram

Instructor: EXCEL 2003 or earlier Click on the TOOLS tab in the top bar of Excel. If your computer already has the “Data Analysis” option listed, you are ready to go. Your Data Analysis tools have already been added. Under the Data Analysis Function you will find some of the more advanced functions that we will be discussing such as ANOVAs, and t tests. If you do not see Data Analysis listed under the TOOLS tab, I encourage you to go to the Help function in Excel for specific instructions on how to load “the Data Analysis Toolpak” in your version of Excel. Here are the easy standard instructions for Excel 2003 if you need to load the Add-Ins. 1) On the Tools menu, click Add-Ins. 2) In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK. 3) When you load the Analysis Toolpak, the DATA ANALYSIS command is automatically added to the TOOLS menu. If your version if slightly different than the above, refer to your HELP function for details on loading Tookpak. Remember that the Data Analysis function is NOT necessary for the course, but it is helpful. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Villanova instructor: Excel 2007 The Analysis ToolPak is a Microsoft Office Excel add-in program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first. 1. Click the Microsoft Office Button , and then click Excel Options at the bottom. 2. Click Add-Ins, and then in the Manage box, select Excel Add-ins. 3. Click Go. 4. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK. If you get prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it. 5. After you load the Analysis ToolPak, the Data Analysis command is available in the ANALYSIS group on the DATA tab. Remember that the Data Analysis function is not required for the course. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: Information about project charters can be found on pages V-2 through V-5 in the Villanova Six Sigma Black Belt Handbook.

Instructor: I am your sponsor for this project. If I was your actual sponsor, I would be very busy and would need to make decisions quickly. I would need to know what this project is all about and how it impacts the strategic objectives of the organization. Please limit this to a sentence or two.

Instructor: Acting as your real-world sponsor, I would need to be sold on why we need to do this project. I wouldn't have time to read long explanations. I would need a short, to-the-point compelling reason why we need to do this. In the problem statement, we 'sell' the need for the project with specific and measureable data.

Instructor: This needs a lot of thought. As your sponsor, I do not want to see 'scope creep.' "What's scope creep?"…please read on... What the scope is not: -It is not merely a timeline (i.e., when the Six Sigma project is to begin and when it is expected to end.) -It is not restating the problem being attacked. The scope defines the boundaries of the project--usually some beginning point and ending point. For example, if I were leading a project to improve the delivery of course materials to students, I would have the following boundaries: The process (IN THIS PROJECT) starts: When the customers says, "Yes, I am interested in enrolling." The process (IN THIS PROJECT) ends: When the customer receives the box from UPS that contains their handbook and CD's. The project stays within that confinement. Included might be: Enrollment process, warehousing process, accounting process, and UPS delivery process. What would NOT be included: -Errors in the handbooks or CDs. -User friendliness of the materials. Any of these items would be fine for some OTHER PROJECT. “Scope creep” is a common phenomenon with teams, but should be avoided. If the scope was predetermined to be as stated above, the team should stick to the confines of that scope. If they do not stick to it, eventually the team might begin to work on “login times.” Then, they might add to that the time it takes the enrollment rep to “close the sale.” Then, they might add to that making sure all materials are delivered error free. ...and on and on it goes. Thee scope creeps on and on. The team soon becomes frustrated and the probably of failure increases. It is best to establish the scope--then, the team needs to stick to it. You may also include important constraints/assumptions including the staffing allocation (team composition) as well as the budget constraints in a scope statement.

Instructor: What is your target improvement for this project, including a target date? George Eckes mentions a 50% improvement as a possible target for Six Sigma projects. Is a 50% improvement enough in this case?

Instructor: If you want your histogram to have the appropriate number of bin ranges (also known as cell intervals), you will need to enter some bin ranges in the dialogue box in DATA ANALYSIS for making Histograms. We will do the first one for you. The largest value you have in the 'length' column is 10.77. The smallest is 10.63. So, the range is 0.14. Add +1 for 'inclusion' (as mentioned in the lecture on cell intervals) = 0.15. The recommended number of cell choices for n = 35 is 5 to 7. I pick 5 to make the math easier. That means I will have 5 cells with 0.03 in each. So, for the first data set the cell intervals are: 10.63-10.65 10.66-10.68 10.69-10.71 10.72-10.74 10.75-10.77 Enter the 'maximum' value (or ending value) from each cell interval into a separate place in this worksheet - location is not important. (i.e., 10.65, 10.68, 10.71, 10.74, 10.77). Then you will be able to drag the cursor across them to satisfy the bin range box in DATA ANALYSIS. Confused? Re-watch the lecture on histograms and cell intervals. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: Excel 2007 - 2 Options Option #1 (Quick and Dirty draft of a histogram) 1) Click DATA tab 2) Go to ANALYSIS category on the DATA tab 3) Click on DATA ANALYSIS (If you do not see DATA ANALYSIS, refer to the EXCEL EXAMPLES tab at the bottom of this spreadsheet for instructions in loading the DATA ANALYSIS features.) 4) Select HISTOGRAMS and then click OK 5) The cursor should be blinking in the "Input Range" box 6) Highlight your data 7) Click in "New Worksheet Ply" 8) Skip the bin range option, and other options 9) Check the "Chart Output" box 10) Click OK 11) You may drag your mouse over the corner of the graph to enlarge it. Option #2 (A More polished copy where you determine the bin ranges) 1) Click DATA tab 2) Go to ANALYSIS category on the DATA tab 3) Click on DATA ANALYSIS (If you do not see DATA ANALYSIS, refer to the EXCEL EXAMPLES tab at the bottom of this spreadsheet for instructions in loading the DATA ANALYSIS features.) 4) Select HISTOGRAMS and then click OK 5) Determine the range of the data set, from your smallest to your largest data value 6) Add one value (in the unit you are measuring) for 'range with inclusion' (Example: .14 (range) + .01 = .15) 7) Determine the appropriate number of bars for your sample size (Example: < 50 data points use 5, 6 or 7 bars) 8) Calculate both the beginning and ending point of each cell and list the beginning and ending points of each bin in separate cells. For example, if you have a range of .15, you could have 5 cells with .03 data value per cell. 9) Now you have an idiosyncrasy of Excel. Excel will give you the option of designating the bin ranges. But, you should give Excel the ENDING value of each bin rather than the beginning value. Highlight the cells that include the ending value of each bin under the BIN RANGE option. 10) Highlight your complete data set under the INPUT RANGE option 11) Check on CHART OUTPUT and where you want the histogram chart located. (New worksheet or imbedded) 12) Click OK 13) You may drag your mouse over the corner of the histogram chart to enlarge it. 14) When Excel drafts histograms, the bars do not adjoin. Ideally, we want the bars of a histogram to adjoin because we are graphing continuous data. To make the bars adjoin, right click on top of bars and choose FORMAT DATA SERIES. In Excel 2007, you go directly to setting GAP WIDTH to zero. 15) To change the color scheme of the histogram, right click somewhere outside of the bars in the graph and choose FORMAT PLOT AREA. You can choose many color formats. 16) When you plan the cell intervals, there are no data points in the MORE category, so you may delete it, because the MORE category is often confusing to people. DO NOT delete the MORE category if it has data points in it. 17) Only go through these more detailed steps if you are interested in a polished Histogram. Otherwise run a quick draft of the histogram with Option #1. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: Excel 2003 - 2 Options Option #1 (Quick and Dirty draft of a histogram) 1) Click TOOLS in the Excel toolbar 2) Click Data Analysis 3) Select HISTOGRAMS and then click OK 4) The cursor should be blinking in the "Input Range" box 5) Highlight your data 6) Click "New Worksheet Ply" 6) Skip the bin range option, and all other options 7) Check the "Chart Output" box 8) Click OK, and your histogram will appear. 9) You may drag your mouse over the corner of the histogram graph to enlarge it. 10) Do NOT delete the MORE category if there are data values in it. Option #2 (A more polished copy where you determine the bin ranges, rather than Excel) 1) Click TOOLS in the Excel toolbar 2) Click Data Analysis 3) Select HISTOGRAMS and then click OK 4) Determine the range of the data set, from your smallest to your largest data value 5) Add one value (in the unit you are measuring) for 'range with inclusion' (Example: .14 (range) + .01 = .15) 6) Determine the appropriate number of bars for your sample size (Example: < 50 data points use 5, 6 or 7 bars) 7) Calculate both the beginning and ending point of each cell and list the beginning and ending points of each bin in separate cells. For example, if you have a range of .15, you could have 5 cells with .03 data value per cell. 8) Now you have an idiosyncrasy of Excel. Excel will give you the option of designating the bin ranges. But, you should give Excel the ENDING value of each bin rather than the beginning value. Highlight the cells that include the ending value of each bin under the BIN RANGE option. 9) Highlight your complete data set under the INPUT RANGE option 10) Check on CHART OUTPUT and where you want the histogram chart located. (New worksheet or imbedded) 11) Click OK 12) You may drag your mouse over the corner of the histogram chart to enlarge it. 13) When Excel drafts histograms, the bars do not adjoin. Ideally, we want the bars of a histogram to adjoin because we are graphing continuous data. To make the bars adjoin, right click on top of bars and choose FORMAT DATA SERIES. In Excel 2003, you will have to select OPTIONS. Then set the GAP to Zero. 14) To change the color scheme of the histogram, right click somewhere outside of the bars in the graph and choose FORMAT PLOT AREA. You can choose many color formats. 15) When you plan the cell intervals, there are no data points in the MORE category, so you may delete it, because the MORE category is often confusing to people. DO NOT delete the MORE category if it has data points in it. 16) Only go through these more detailed steps if you are interested in a polished Histogram. Otherwise run a quick draft of the histogram with Option #1. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: What is expected variation? We know that 68% of data from a normal process is expected to fall + or - 1 sigma from the mean. We know that 95% of data from a normal process is expected to fall + or - 2 sigma from the mean. We know that 99.7% of data from a normal process is expected to fall + or - 3 sigma from the mean. So, the expected variation that we would likely see in any normally distributed data will fall + or - 3 sigma. Any value above +3 standard deviations or below - 3 standard deviation is considered to be "assignable-cause" variation and needs to be dealt with according. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: What is expected variation? We know that 68% of data from a normal process is expected to fall + or - 1 sigma from the mean. We know that 95% of data from a normal process is expected to fall + or - 2 sigma from the mean. We know that 99.7% of data from a normal process is expected to fall + or - 3 sigma from the mean. So, the expected variation that we would likely see in the data will fall + or - 3 sigma. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: Excel 2003 1. Highlight the data cells that you want to modify. 2. Click on FORMAT at the top Excel bar. 3. Click on CELLS 4. Under the NUMBER tab, choose the Number Category 5. Set the number of decimal places to your choice. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: Excel 2007 1. On the HOME tab, find the NUMBER category. 2. Click on the drop down arrow by the word GENERAL. 3. Selected MORE NUMBER FORMATS. 4. Specify the number of decimal points. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: Want to use Excel 2003 to find the mean and standard deviation? 1. Click Tools in the top bar 2. Click Data Analysis (If you don't see Data Analysis as an option on the TOOLS menu, go to the 'EXCEL EXAMPLES' tab at the bottom of this spreadsheet for instructions to load the Data Analysis Tookpak.) 3. Click Descriptive statistics 4. OK 5. Drag with mouse to capture the data. 6. New Workbook Ply 7. Summary statistics 8. OK (You probably will need to resize your column widths to fully see the chart) You may also reset the number of decimal values that are visible, by clicking FORMAT - CELLS - NUMBER and setting the decimal place. Note: You can also do this on a simple scientific calculator. If you want to learn how to do it on a calculator, refer to the bonus lecture MATH PRIMER. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: Want to use Excel 2007 to find the mean and standard deviation? 1. Click the DATA tab in the top bar 2. In the ANALYSIS category, click on Data Analysis (If you don't see Data Analysis as an option go to the 'EXCEL EXAMPLES' tab at the bottom of this spreadsheet for instructions to load the Data Analysis Tookpak.) 3. Click Descriptive statistics 4. OK 5. Drag with mouse to capture the data. 6. New Workbook Ply 7. Check Summary statistics 8. OK (You probably will need to resize your column widths to fully see the chart) You may also reset the number of decimal values that are visible, by going to the NUMBER category on the HOME page. Click on the drop down menu and select MORE NUMBER FORMATS. Select number and specify the number of decimal points that you want to view in your spreadsheet. Remember Excel stores the entire value in the cell. You are just designating how many decimal places are displayed in the cell. Note: You can also do this on a simple scientific calculator. If you want to learn how to use a calculator, refer to the bonus lecture MATH PRIMER. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: You are given the mean of the population. It comes from the old method's historical data.

Instructor: From the data below.

Instructor: From the data below.

Instructor: A quick refresher on hypothesis statements. I recommend starting off with thinking about what you are trying to test. In this case, we are attempting to test if the old method and the new method are producing different averages/means. This becomes our alternative hypothesis. The null hypothesis is always what we would expect by chance alone. In this example, both the old method and the new method are expected to create the same product dimension. Therefore, we would expect the old method average and the new method average to be equal. The alternative hypothesis, in contrast, is attempting to test if the methods are producing different average dimensions of product. Now you should be able to write your Null and Alternative Hypothesis in standard format. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: Since µ is given, using the Data Analysis function is of little use. You could set up the formula manually in Excel, but it is faster to do in on paper with a simple calculator per formula in the lecture on the Student T Distribution in your white manuals. See the T Table in the online textbook. To make sure you are using the correct formula you will be using µ, X-bar, s (from your own calculation), and n. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: If the calculated T is greater than the critical T, there is 95% confidence that the means are statistically different. In other words, the null hypothesis can be rejected. In other words, the data suggest there is a difference in the means.

Instructor: Did you read all of the other helpful notes on this sheet? Did you watch the lecture the Student's T Distribution?

Instructor: Simply put: Are you just looking to see whether or not there is a DIFFERENCE… 2 tail (In other words, are they equal to each other, or not?) 1 tail Are you looking for any of these: <, >, =>, ,<=

Instructor: You should get a value between 0 and 0.030

Instructor: You should get a value between 0 and 0.030

Instructor: You should get a value between 0 and 0.010

Instructor: You should get a value between 1 and 5. ( This value could be either positive or negative. )

Instructor: You should get a value between 0 and 5.

: Explain how you came to this conclusion. It is not enough to say "reject or fail to reject" the null. Tell how the data helped you draw your conclusion at this confidence level. This should only take 2 or 3 sentences.

: Write one sentence on the position the company takes on the issue described in the above scenario. This is the opinion of the company about the current condition. Writing the claim statement will help you phrase your hypotheses and conclusion.

Instructor: You should get a value between zero (0) and 0.10

Instructor: You should get a value between zero (0) and 10. Hint: Are you using the correct alpha?

Instructor: A quick refresher on hypothesis statements. I recommend starting off with thinking about what you are trying to test. In hypothesis testing, you are testing to see if there has been a change (something is different, something has increased, something has decreased.) That is the 'alternate hypothesis.' For the null, it is simply the opposite of that, or what we would normally expect. If I had a Corvette and a 3000-GT, I would normally expect these cars to have approximately the same gas mileage. But, I want to test if there is a statistically significant difference in the MPG. If I am trying to prove that a Corvette gets better mileage than a 3000-GT, I am testing to see if there is a statistically significant difference between the two cars, and that becomes my 'alternate hypothesis'--or the thing I am trying to test. H-Alt: Corvette > 3000-GT H-Null: Corvette = or < 3000-GT

Instructor: To write your conclusion, you have 2 conclusion options. 1) fail to reject the NULL 2) reject the NULL. Think about it…if you were trying to prove that the Corvette's mileage (refer to the previous help note) was better than that of the 3000-GT, and you were correct, you would have rejected the NULL. You are always trying to reject the NULL to prove your point. If you fail to do that, you didn't accept the NULL--instead you failed to reject the NULL. If you understand these two notes thoroughly, you are probably better off than most of the practicing black belts out there. Don't believe me?...ask them to explain the difference, then watch their facial expression. Recommended hypothesis conclusions: A) If you reject the null, you are able to conclude the alternative hypothesis statement is true at x% confidence level. B) But, if we fail to reject the null, we only have two options for a conclusion statement... 1) We fail to reject the null at x% confidence. 2) We fail to reject the null at x% confidence, and therefore, there is insufficient evidence to conclude that the alternative statement is true. In business, sometimes we 'assume' that the null is true, if we can't disprove the null. But we need to be cautious here. We should not state the null as 'fact', as if we have proven it true at any confidence level. Your hypothesis test has never proven the null true, and you do not want to base a significant business decision on the fact that you have proven the null true, without other types of testing that would also lead you to the conclusion that the null is true.

Instructor: See the Chi-Square topic in your online textbook for a more detailed discussion of the Chi Square Test for Independence. The chi square test for independence is always a one-tail, right tail test.

Instructor: You may solve this chi square assignment with Excel, but you will be using two different Excel functions, 'Chitest' and 'Chiinv.' Chitest gives you the p-value when you compare the actual range with the expected range. If your p-value is less than your alpha, you may reject the null. If you want to convert the p-value to your chi square test statistic, use the 'chiinv' to transform the 'probability' statistic to your 'chi square test statistic.' Although you may use Excel in this assignment, you first must still calculate the 'Expected Values" as described in the Sample exercise. Here are the steps..... 1. Now you should have a table with the actual counts, and a separate table with the expected values. 2) If you have an fx in your top Excel bar, click on the fx and type in CHITEST, and OK. If you do not have an fx on the home page, click on INSERT in the top menu and then click on FUNCTION. Next type in CHITEST. 3) Once you have the pop up screen for CHITEST, highlight the data for both the ACTUAL RANGE and the EXPECTED RANGE. Click on OK. 4) The statistical result that you are seeing is the p-value. If your p-value is < your alpha level, you may reject the null. 5) If you would like to convert your p-value to a chi square test statistic, click on fx again and select CHIINV. The CHIINV will convert the p-value to your chi square test statistic. You will then compare this result with your chi square critical value. Since this assignment requires that you report the chi square test statistic, you will be using both the CHITEST function and the CHIINV if you solve the assignment with Excel. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: You may solve this chi square assignment with Excel, but you will be using two different Excel functions, 'Chitest' and 'Chiinv.' Chitest' gives you the p-value when you compare the actual range with the expected range. If your p-value is less than your alpha, you may reject the null. If you want to convert the p-value to your chi square test statistic, use the 'chiinv' to transform the 'probability' statistic to your 'chi square test statistic.' Although you may use Excel in this assignment, you first must still calculate the 'Expected Values" as described in the Sample exercise. Here are the steps... 1. Now you should have a table with the actual counts, and a separate table with the expected values. 2) If you have an fx in your top Excel bar on the HOME tab, click on the fx and type in CHITEST, and OK. If you do not have an fx on the home page, click on the FORMULAS tab and then click on the fx in the Functions Library category. 3) Once you have the pop up screen for CHITEST, highlight the data for both the ACTUAL RANGE and the EXPECTED RANGE. Click on OK. 4) The statistical result that you are seeing is the p-value. If your p-value is < your alpha level, you may reject the null. 5) If you would like to convert your p-value to a chi square test statistic, click on fx again and select CHIINV. The CHIINV will convert the p-value to your chi square test statistic. You will then compare this result with your chi square critical value. Since this assignment requires that you report the chi square test statistic, you will be using both the CHITEST function and the CHIINV if you solve the assignment with Excel. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

: Explain how you came to this conclusion. It is not enough to say "reject or fail to reject" the null. Tell how the data helped you draw your conclusion at this confidence level. This should only take 2 or 3 sentences.

: Write one sentence on the position the company takes on the issue described in the above scenario. This is the opinion of the company about the current condition. Writing the claim statement will help you phrase your hypotheses and conclusion.

Instructor: "Of course you want to use Excel--who wouldn't?" But.... You need to know how to calculate ANOVA the hard way (below) if you plan to sit for the ASQ test. I guarantee you will be asked at least one question on these calculations. This is perhaps why our students have such an outstanding pass rate (90%+). For Excel 2003, follow this sequence: -Tools -Data Analysis -ANOVA-Single Factor -OK -Input range [To get this, drag from the upper left to the lower right of the data set. In other words, from 'Machine 1 (including the words "Machine 1" diagonally to the bottom right-hand corner 0.572) -Check 'Labels in first row' -Check 'New workbook Ply -OK If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: Sum of machine 1 results

Instructor: sample size for machine 1

Instructor: Just like it appears: First sum the data from Machine 1. Then square that result. Next, divide by the n of machine 1. You should get a number between 1 and 2.

Instructor: Square each of the machine 1 values--one at a time. Add them up. You should get a number between 1 and 2.

Instructor: Sum of machine 2 results.

Instructor: n of machine 2

Instructor: First sum the data from Machine 2. Then square that result. Next, divide by the n (number of samples) for Machine 2.

Instructor: Do the same as above, but for machine 2.

Instructor: Sum of machine 3 results

Instructor: n of machine 3

Instructor: First sum the data from Machine 3. Then square that result. Next, divide by the n (number of samples) for Machine 3.

Instructor: Do the same as above, but for machine 3.

Instructor: Total of this column. You should get a number between 5 and 10.

Instructor: Total of this column. You should get a number between 10 and 15.

Instructor: Total of this column. You should get a number between 1 and 5.

Instructor: Total of this column. You should get a number between 1 and 5.

Instructor: How many data do you have in total? n-1 You should get a number between 5 and 15. Plug that number into the ANOVA table below.

Instructor: What are the factors? The factors are the machines 1, 2 & 3 in this case. n - 1 Plug that number into the ANOVA chart below.

Instructor: CM: Mean 'Correction for the Mean.' To get this number, you sum all of your data values and then square that value. Next, you divide by the total number of data points.

Instructor: Refer to the assistance table above. You subtract the CM from the forth column's total. Plug that number into the ANOVA table below. You should get a number approximately 0.00xxxx)

Instructor: Column 3's total minus CM. Plug that number in the ANOVA table below. You should get a number 0.000xxxx)

Instructor: Subtract step 6 from step 5. Plug that number into the ANOVA chart below. You should get a number (0.00xxxx)

Instructor: Self explanatory. You should get a number (0.000xxxx) Plug that number into the ANOVA chart below.

Instructor: Self explanatory. You should get a number (0.000xxxx) Plug that number into the ANOVA chart below)

Instructor: Self explanatory. You should get a number between 0 and 1. Plug that into the ANOVA chart below.

Instructor: With ANOVA it is always a one-tail, right-hand tail. You should get a number between 1 and 5.

Instructor: SS-Factor: ~0.000xxx SS-Error: ~0.00xxxx SS-Total: ~0.00xxxx df-factor: 0 - 5 df-error: 5 - 10 dfTOTAL: 10-15 MS-Factor: ~0.000xxx MS-Error: ~0.000xxx Calc-F: 0 - 1 F-Crit: 0 - 5

Instructor: "Of course you want to use Excel--who wouldn't?" But.... You need to know how to calculate ANOVA the hard way (below) if you plan to sit for the ASQ test. I guarantee you will be asked at least one question on these calculations. This is perhaps why our students have such an outstanding pass rate (90%+). For Excel 2007, follow this sequence: -Click on the DATA tab -Go to the ANALYSIS category -Click on Data Analysis -Select ANOVA-Single Factor -OK -Input range [To get this, drag from the upper left to the lower right of the data set. In other words, from 'Machine 1 (including the words "Machine 1" diagonally to the bottom right-hand corner 0.572) -Check 'Labels in first row' -Check 'New workbook Ply -OK If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

: State if you reject or fail to reject the null in terms of the evidence presented by the data. Explain how the evidence lead to your conclusion at the stated confidence level.

: Write one sentence on the position the company takes on the issue described in the above scenario. This is the opinion of the company about the current condition. Writing the claim statement will help you phrase your hypotheses and conclusion.

Instructor: Please re-watch Patrick's lecture on Analyzing Full Factorial Designs.

Instructor: Whenever you want to enter either + or - you will need to hit "ENTER" each time. If you enter + or - and try to use the arrow keys, each cell will think you are trying to create a formula. Try it to see what I mean.

Instructor: Note: Whenever you want to enter either + or - you will need to hit "ENTER" each time. If you enter + or - and try to use the arrow keys, each cell will think you are trying to create a formula. Try it to see what I mean.

Instructor: Here's a memory jogger. Remember how we multiplied columns together to find out about the interaction columns? If not, please re-watch the lecture on Analyzing Full Factorial Designs.

Instructor: 1) Click on the fx in the top bar and CORREL, or click on FOMULAS, INSERT function, CORREL. 2) Highlight each column of data as an ARRAY 3) Click Okay. 4) Excel will calculate the correlation coefficient. The correlation coefficient ranges between zero and one. Zero is no correlation and '1' is a perfect correlation. __________________________________________ -1.0 to -0.7 strong negative association. -0.7 to -0.3 weak negative association. -0.3 to +0.3 little or no association. +0.3 to +0.7 weak positive association. +0.7 to +1.0 strong positive association. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: 1) Click on the fx in the top bar and CORREL, or click on INSERT, function, CORREL. 2) Highlight each column of data as an ARRAY 3) Click Okay. 4) Excel will calculate the correlation coefficient. The correlation coefficient ranges between zero and one. Zero is no correlation and '1' is a perfect correlation. __________________________________________ -1.0 to -0.7 strong negative association. -0.7 to -0.3 weak negative association. -0.3 to +0.3 little or no association. +0.3 to +0.7 weak positive association. +0.7 to +1.0 strong positive association. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: 1) Highlight both rows of data 2) Click INSERT tab at the top 3) Go to CHART category 4) Click on SCATTER and your scatter diagram appears If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: 1) Click on Chart icon on top task bar, OR Click on the INSERT menu option at the top menu bar. 2) Click on SCATTER from the Standard Types tab 3) Click Next 4) Highlight both columns of data and finish according to the directions. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Calculate the correlation coefficient to help you make this decision.

Instructor: This is Step 6 in control charting. Refer to the lectures on Control Charts.

Instructor: You should get a value between 0 and 1.

Instructor: You should get a value between 0 and 1.

Instructor: You should get a value between 0 and 0.0200.

Instructor: You should get a value between 0 and 0.0100.

Self check: UCL between 0.3 and 0.6 LCL between 0.3 and 0.6

Instructor: Please refer to the lecture on how to calculate control limits for the XmR chart.

Self check: You should have gotten a value between 0.0050 and 0.0200.

Instructor: Please refer to the lecture on how to calculate control limits for the XmR chart.

Self check: Did you get 0.000176? If you did, you did not use absolute values. Instead you used values that contained negative and positive numbers. You should have gotten a moving average range value of 0.003471 or rounded to 0.0035.

Instructor: More help on calculating R-bar using Excel 2007 We really intend on you doing this step by hand, but here is an option in Excel. The average moving range is the average of all of the ranges of subgroup size of 2. For more information about a moving range, you need to revisit the lecture on the XmR chart (Lecture 82). Calculating your average moving range with Excel is a 2-step process. First you need to find the absolute range values for the ranges of each subgroup (size =2). Why the absolute value? If you just calculate the difference between any two cells, you would get both positive and negative numbers. You do not want negative numbers. Absolute values are numbers that are only in the 'positive' form. We will do the first one for you. To find the absolute range value for the first value (0.551) and the second value (0.546) do the following steps in Excel. 1) Click the empty cell next to, and to the right of the second value (0.546) 2) Click the FORMULAS tab at the top bar 3) Under the FUNCTION LIBRARY category, click MATH & TRIG 4) Scroll down to 'ABS' (absolute value) 5) OK 6) Click on the first cell (0.551) 7) Type in a minus from the keyboard, and click on the second cell (0.546) 8) Enter. You should get 0.005. 9) Now....grab the bottom right-handed corner of the cell you are working with, and drag it all the way down to the bottom of the list of numbers. This will repeat the formula for you all the way down the line. You should have all of the range values starting at 0.546 and ending with the last value of 0.545. 10) For the second step in this process, you will be taking an average of all of your range values to get the average moving range. To do that: 11) Click on any empty cell 12) Insert 13) Function 14) Scroll down to 'AVERAGE' 15) OK 16) Drag down the column of values that you just created in the previous step. 17) OK. You have just calculated the average moving range which you will use in calculating the control limits for the XmR chart. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: More help on calculating R-bar using Excel 2003 We really intend on you doing this step by hand, but here is an option in Excel 2003 & earlier. The average moving range is the average of all of the ranges of subgroup size of 2. For more information about a moving range, you need to revisit the lecture on the XmR chart (Lecture 82). Calculating your average moving range with Excel is a 2-step process. First you need to find the absolute range values for the ranges of each subgroup (size =2). Why the absolute value? If you just calculate the difference between any two cells, you would get both positive and negative numbers. You do not want negative numbers. Absolute values are numbers that are only in the 'positive' form. We will do the first one for you. To find the absolute range value for the first value (0.551) and the second value (0.546) do the following steps in Excel. 1) Click the empty cell next to, and to the right of the second value (0.546) 2) INSERT 3) FUNCTION 4) Scroll down to 'ABS' (absolute value) 5) OK 6) Click on the first cell (0.551) 7) Type in a minus from the keyboard, and click on the second cell (0.546) 8) OK. You should get 0.005. 9) Now....grab the bottom right-handed corner of the cell you are working with, and drag it all the way down to the bottom of the list of numbers. This will repeat the formula for you all the way down the line. You should have all of the range values starting at 0.546 and ending with the last value of 0.545. 10) For the second step in this process, you will be taking an average of all of your range values to get the average moving range. To do that: 11) Click on any empty cell 12) Insert 13) Function 14) Scroll down to 'AVERAGE' 15) OK 16) Drag down the column of values that you just created in the previous step. 17) OK. You have just calculated the average moving range which you will use in calculating the control limits for the XmR chart. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: Whether or not a measurement system is discriminate, is covered in two different lectures. It is covered in the control chart lecture and it is also covered in the 'Measurement System Evaluation" lecture.

Instructor: The formulas for the control limits of the XmR chart are found in your student workbook. Do you understand why we are using a XmR chart in this assignment versus an X-barR chart? ANSWER - We only have individual data points. We do not have rational subgroups.

Instructor: Steps for drafting a control chart in Excel 2007 1. Highlight the data. 2. Click on INSERT tab at top of bar. 3. Find the CHARTS category 4. Click on LINE 5. Add control limits and the mean and average moving range with the drawing tools. (INSERT - SHAPES) If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: 1. Click on chart icon in top menu, OR click on INSERT in menu bar and select CHART. 2. Select LINE chart. 3. Follow menu-driven steps and highlight the data. 4. When the line chart is complete, add the mean, the average moving range, and your control limits with the drawing tools. TOOLBARS - Drawing If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: At the sides of the rows are self-checks for each Pp and Ppk. I don't give you the answer, but at least you will be able to see if you are in the ballpark.

Instructor: You can do part of this with Excel 2003l, but you will have to do some of it by hand. You need 4 values to calculate Pp and Ppk. If you have a 2-sided tolerance like a nominal-is-best quality target, you need to know: Mean s or standard deviation Upper Spec. Lower Spec. To get the MEAN and 'S', Follow this sequence: -Tools -Data Analysis -Descriptive Statistics -Drag down the values -Check the 'Summary Statistics; box. -OK Note: The spec. limits can be found at the bottom of each column. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Instructor: The data are in columns below. At the bottom of each column has the spec. limits (upper and lower). You need 4 values to calculate Pp and Ppk. You need to know: Mean s (standard deviation) Upper Spec. limit Lower Spec. limit You will have to calculate the MEAN and standard deviation for each. Once you have done that, the only caution is to ensure you are using the correct formula for Ppk. Always select the smallest Ppk value from the 2 Ppk formulas because the smaller number represents the closest point of trouble for your process.

Instructor: Pp: Some value between 1 and 5. Ppk: Some value between 1 and 5.

Instructor: What question should you ask if you see no variation? Hmmm.

Instructor: Pp: Some value between 1 and 2. Ppk: Some value between 1 and 2.

Instructor: Pp: Some value between 1 and 2. Ppk: Some value between 1 and 2.

Instructor: Pp: Some value between 1 and 2. Ppk: Some value between 1 and 2.

Instructor: Pp: Some value between 2 and 3. Ppk: Some value between 1 and 2.

Instructor: Pp: Some value between 7 and 8. Ppk: Some value between 6 and 7.

Instructor: Pp: Some value between 8 and 9. Ppk: Some value between 7 and 8.

Instructor: You can do part of this with Excel 2007, but you will have to do some of it by hand. You need 4 values to calculate Pp and Ppk, if you have a 2-sided tolerance like a nominal-is-best quality target as we do in this example. You need to know: Mean s or standard deviation Upper Spec. Lower Spec. To get the MEAN and 'S', Follow this sequence: -Click on the DATA tab at the top bar -Go to the ANALYSIS category -Click on Data Analysis -Click on Descriptive Statistics -Drag down the values -Check the 'Summary Statistics' box. -OK Note: The spec. limits can be found at the bottom of each column. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.