Carillon Technologies Limited

Scatter Diagrams, and Trendlines using Excel
Creating a Scatter Plot and Trend Analysis Using Excel

Example: Using a scatter plot and trend analysis to analyze a measurement system

All Scatter plot require two matching sets of data. In this case we are using thirty parts measured twice by the same analyst with two different micrometers. (Measurement 1 - Micrometer A, Measurement 2 - Micrometer B)

Steps:

Step 1. Sample 30 pieces.
Step 2. Identify each piece with a sequential number.
Step 3. Measure each of the 30 pieces.
Step 4. Measure each of the 30 points again in a random order.
Step 5. Place the corresponding measurements for piece one in adjacent columns in the second row, piece two in adjacent columns in the third row, etc.
 

Step 6. Highlight the sixty data values.

Step 7. Click on the Chart Wizard.

Step 8. Select the XY Scatter chart.

Step 9. Click Next for Step 2 of 4 under Chart Options

Step 10. Under titles fill in the Chart title, Value x and Value y.

Under Legend click off the legend box.

Under Axes add vertical axes.

Finally,

Click Next

Step 11. Under Chart Location click either new sheet or object.

Click Finish.

 

Step 12. Formatting and adjusting the axes and background. Change the minimum, major unit, minor unit to agree between both x and y axes. Click on the background and change color to white from the standard gray. -----> Scatterplot done.

Adding a Trendline (Linear Regression equation)

Step 1. Click on the scatter points on the graph.

Step 2. Go to pull down menu under chart click Add trendline.

Step 3. Click Options folder tab.

Step 4. Click Display on chart box and then click OK

Final Graph should look like this. You might have to adjust the graph to make the grid square.

The equation for the straight line in the form y=mx+b. Where m is the slope of the line and b is the y intercept.

Continue Measurement Analysis