UNIT 7 CHARTS

Charts are a useful tool to display numerical data; to convert it from data to information by providing a visual image. The Chart used in Quality Control provides a visual image of sample measurement means and Limits of expected values. Histograms convert raw measurement data to a picture of Process Capability. Charts frequently show the relationship between variables such as voltage and current.

OBJECTIVES

After completing this chapter, the student will be able to:

• Draw technical charts.
• Include charts in the solution of technical problems.
• Use charts to provide regression analysis
• Use histograms to analyze data
• Chart the normal distribution.
• Create Charts from sample data.

CHART ELEMENTS

All charts contain a vertical axis, the Y-axis, a horizontal axis, the X-axis, and a title. Many charts with more than one data series also contain a legend. The EXCEL Chart Wizard automatically provides an opportunity to add these to a chart.

Y axis (vertical)

Each axis must be labeled with both the variable name and the unit of measure as "diameter in inches". The variable is "diameter" and the unit of measure is "inches". The Y-axis is often refereed to as the "value" axis.

X axis (horizontal)

The scatter chart is the only EXCEL chart type that uses values on the X-axis. It is often referred to as the category axis because the other chart types treat all X-axis data as "categories" rather than as values.

Title The chart title is a few words to indicate the chart purpose.

Data Markers

The data marker is the symbol that marks the X and Y-value point for a given data element. Data markers may be connected by lines.

Legend

Some charts contain two or more lines or data series. The title of each series next to its chart symbol is displayed in a small box to make the chart easily understandable.

Creating a Chart

EXCEL includes a Chart Wizard that simplifies the creation of charts from columns or rows of data. This chapter will cover only three of the many chart types available, the column chart, the line chart, and the scatter chart.

Column Chart

The column chart uses column height to show the numbers of things as they relate to some condition shown on the horizontal X-axis. The histogram is a column chart showing the number of measurements between cell limits.

Line Chart

The line chart shows the relationship between the Y-values and the X-values but requires that the X data be evenly spaced. Where the X-values are titles such as sample numbers or month names, this is not a problem. In technical applications where X-values are measurements, this "even spacing" requirement is dangerous. The X-values the line chart ignores the quantity of the X-values and will evenly space every X-value whether or not the measurements actually are evenly spaced. For this reason, the XY or scatter chart is the technical chart of choice.

Scatter Chart

A scatter, XY, chart shows the relationships among the numeric values of two or more data sets. The one on the left shows the relationship between the volume and weight of steel objects.

Creating a Chart

The EXCEL Standard Toolbar includes a Chart Wizard button that starts the chart making process. By the end of this process a complete chart is has been created. The Chart Wizard menus vary slightly among the several versions of EXCEL but are similar enough that an example from EXCEL 97 will provide the information needed to follow the other versions.

EXAMPLE 1

Before staring the Chart Wizard, arrange the values in adjacent columns or rows. By default, the left column becomes the X-axis value series. A chart can be created from cells or ranges that are not adjacent. Select the first group of cells; then while holding down the Ctrl key, select additional cell groups. In this example, the columns including the headings RADIUS and AREA were selected.

Clicking the Chart Wizard button brings up step 1 of the Chart Wizard. (Earlier versions of EXCEL require that after clicking the button, you designate a chart location by clicking on the worksheet and dragging a rectangle.} The XY (Scatter) chart is selected. Then the sub-type is selected. ( In earlier versions of EXCEL the subtype, selection is a separate step.)

The second Chart Wizard step displays the chart that the Wizard is prepared to complete. In this case, it correctly determined one line from the data on Columns. Had we wanted four lines from the data in rows, clicking the Row button would change the chart. The axis values have the same format as the cell values including the units. The default includes a Legend box and that the text at the top of the right column was chosen for a tentative chart title.

The Series tab on Chart Wizard step 2 shows the sheet location of the values that are on each axis and the chart lines. It provides an opportunity to swap X and Y-axis values. This will be necessary in creating the Chart. The Series tab on Chart Wizard step 2 shows the sheet location of the values that are on each axis and the chart lines. It provides an opportunity to swap X and Y-axis values.

Step 3 allows the addition and changes to the chart title and axis labels. The Legend tab allows the repositioning or deletion of the Legend. In this example, the legend is deleted.

Step 4 provides an opportunity to make the chart a separate sheet. Clicking "Finish" results in the chart below.

Problem set 7

Create the graphic solutions shown below Put each problem on a separate sheet. Send the solutions to your instructor.

1

2

3

A chart can be created from cells or ranges that are not adjacent. Select the first group of cells; then while holding down the Ctrl key, select additional cell groups.

4

A cylinder has the following specifications: H = 3.000 ± 0.010 in. D = 2.000 ± 0.010 in. Plot the volume for the smallest, nominal mid-spec.), and maximum dimensions.

Using Charts to Analyze Data

The charts above showed the relationships among dimensions and calculated values. The lines on the charts are expressed by equations. The example above used the equation A = 3.14R². The A is plotted on the Y-axis. The R is plotted on the X-axis. The line on the line could be expressed as Y = 3.14X².Many charts show the relationship between measured values.

Chart data analysis

This chart shows the relationship between the measured volume and weight of several brass balls.

EXAMPLE Density from Trendline

The Trendline process starts with an XY, Scatter, chart with data markers but no line.

On the chart, select the data markers. In EXCEL 97 click on a marker. ( In earlier versions double click the chart to get it active then click a marker.)

In EXCEL 97, the Add Trendline is under the Chart menu. In earlier versions, use the Insert Trendline under the Insert menu.

Select the Linear Trendline

On the Options Tab, select "Set intercept = 0" and "Display equation on chart" by clicking the boxes next to these choices.

The result is a chart displaying

• The measurement data as markers.
• A straight line that best fits the measurement data.
• The equation for the line with the calculated value of m in the equation.

The complete format for presenting this data analysis would be.

PROBLEM SET 8

Create the graphic solutions for the problems below using the format shown above. Put each problem on a separate sheet. Send the solutions to your instructor.

2. Voltage across a resistor is calculated by the formula E = IR where I is the current in amps and R is the resistance in ohms. For the two data sets below graph the data with I on the X-axis. Use the trendline option, which shows the regression formula to estimate the resistance. (Select the zero intercept option.)

DATA ANALYSIS

HISTOGRAMS

Histograms are useful in analyzing data by showing the shape of the value distributions. In its Quality control application it is a column chart that shows how many times, frequency, a particular measurement is produced. In the figure below, it shows that the value 14 appears 18 times.

The X-axis of the Histogram shows the Bin Range. These values are in ascending order. Microsoft Excel counts the number of data points between the current bin number and the adjoining higher bin, if any. A number is counted in a particular bin if it is equal to or less than the bin number down to the last bin. All values below the first bin value are counted together, as are the values above the last bin value.

If no Bin values are provided EXCEL will divide the data into five columns which is usually too few for a good picture of the process. There is no magic number, but the frequency in the outer columns should trail toward 1 or zero.

The histogram below shows the distribution of 100 measurements that appear to be normally distributed. Although they were taken in twenty samples of five measurements each the histogram works only with the values and ignores their grouping.

Bin numbers were selected to include the smallest and largest sample values and were typed in cells H3: H16. Everything right of column I was generated by the Histogram Tool.

HISTOGRAM PROCEDURE

1. The values are entered into the spreadsheet in a column, a row, or a combination. In the figure above, they are cells B2 through F21.
2. The histogram Bin numbers are typed either in a row or in column. The histogram analysis will select its own cell midpoints if none are provided. In the figure above, they are cells H3 through H16.
3. If the DATA ANALYSIS option is not shown at the bottom of the TOOLS menu it can be loaded by choosing Add-Ins on the TOOLS menu and selecting the two Analysis ToolPak from the Add-Ins dialog box
4. 
5. Select Data Analysis on the TOOLS menu.
6. Select Histogram from the Data Analysis dialog box and get the Histogram dialog box.

Place the cursor in the Input Range window and then select the cells containing the values to be analyzed. Place the cursor in the Bin Range window and select the cell containing the cell midpoints you want the analysis to use. Click the Output Range button, place the cursor in the Output Range window and select the cell where you want the output data to start. Click the box next to the type chart you want. Click OK.

The Output Options are:

Output Range

Enter the reference for the upper-left cell of the output table. Microsoft Excel automatically determines the size of the output area and displays a message if the output table will replace existing data.

New Worksheet Ply

Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. To name the new worksheet, type a name in the box.

New Workbook

Click to create a new workbook and paste the results on a new worksheet in the new workbook.

Pareto (sorted histogram)

Select to present data in the output table in descending order of frequency. If this check box is cleared, Microsoft Excel presents the data in ascending order and omits the three rightmost columns that contain the sorted data.

Cumulative Percentage

Select to generate an output table column for cumulative percentages and to include a cumulative percentage line in the histogram chart. Clear to omit the cumulative percentages.

EXAMPLE Histogram

GIVEN: (All measurements are in grams)

FIND: Histogram data display

Step1: Enter the data into an EXCEL spreadsheet. Step2: Use the MIN and MAX functions to find the measurement range. Step 3:Subtract the Min from the Max to calculate the range Step 4 Start the Bin range with the Min measurement Step 5 Use 9 as an increment to get about a dozen Bin values. The formulas for the calculations are shown in the right column.

Input Range: Drag the cells containing measurement data. Bin Range: Drag the cells containing the Bin values. Output Options: Check Chart Output and a location. The Histogram output is shown below. The chart will normally require some reformatting to change sizes and remove a Legend.

The Normal Distribution

The normal distribution is the distribution of many random events. It is the shape of measurement distributions in most manufacturing activities. It is the mathematical basis for Quality Control Charts relating to measurements.

The measurements used for the Histogram are normally distributed. A Normal distribution is described by two values. The mean or average determines its center. The standard deviation determines its width.

To get the average of the measurements above use the AVERAGE function and get =AVERAGE(). Place the cursor between the parentheses and drag the measurement cells and get =AVERAGE(B4:F23). The average is 396.33 grams. To get the standard deviation repeat the process with the STDEV function and get =STDEV(B4:F23). The standard deviation is 20.49 grams. Statistically, 100 values are enough to provide a good estimate of a population's average and standard deviation. 150 values should be right on.

The NORMDIST Function

Returns the normal cumulative distribution for the specified mean and standard deviation. This function has a very wide range of applications in statistics, including hypothesis testing.

NORMDIST(x,mean,standard_dev,cumulative)

X is the value for which you want the distribution.

Mean is the arithmetic mean of the distribution.

Standard_dev is the standard deviation of the distribution.

Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, NORMDIST returns the total probability for all values up to and including the X value. If FALSE it returns a probability for the X value and provides values for charting the Normal curve.

The Normal Curve

The X-axis values for the normal curve are centered on the mean and extend at least three standard deviations in each direction. The Y-axis values will be provided by the NORMDIST function. The Y-axis values are similar to the "Frequency" values in the Histogram. The numerical values for Y-axis have little significance except to shape the curve.

After selecting the two columns of numbers, create an XY plot with the smooth curve. Edit the chart so that the X-axis starts close to the curve and delete the Legend.

Adding Text

To add text, select the chart; type the text on the formula bar and hit return. The text will pop up on the chart and can be dragged to the desired location Type USL. Hit return. Drag the text box to the upper specification line.

The (X Bar) Chart.

The Chart is an often-used control chart. It is the plot of sample averages, 's, on a graph which also includes line for the highest and lowest expected values for sample averages, UCL and LCL.

Special Formatting

The Chart above and the spreadsheet supporting it use the quality control symbols wherein averages are indicated by a bar above the variable symbol. Special symbols are:

• R for sample range, The largest measurement value minus the smallest measurement value in a single sample.

is the average R for several samples. X is a single measurement value. is the average measurement in a single sample. is the average of several sample averages. The symbols with the bars are created in Equation Editor

The UCL and LCL values shown on the chart are formatted numbers. To show units such as in³ the numbers were formatted as 0.00 "in³". For the control chart the numbers will be formatted "LCL "0.00 and "UCL "0.00.

The Data and Calculation

GIVEN: (All measurements are in grams)
	MEASUREMENT
SAMPLE	X1	X2	X3	X4	X5	Sample Range	SAMPLE	Sample Mean	LCL		UCL
1	392	391	360	401	389	41	1	386.6	366.32	396.33	426.35
2	410	393	442	379	425	63	2	409.8	366.32	396.33	426.35
3	387	408	374	379	387	34	3	387.0	366.32	396.33	426.35
4	379	400	436	394	363	73	4	394.4	366.32	396.33	426.35
5	423	390	368	389	378	55	5	389.6	366.32	396.33	426.35
6	376	381	404	371	411	40	6	388.6	366.32	396.33	426.35
7	356	408	398	398	429	73	7	397.8	366.32	396.33	426.35
8	446	392	376	390	404	70	8	401.6	366.32	396.33	426.35
9	407	405	354	413	388	59	9	393.4	366.32	396.33	426.35
10	417	377	420	383	403	43	10	400.0	366.32	396.33	426.35
11	422	395	393	394	401	29	11	401.0	366.32	396.33	426.35
12	399	436	405	404	394	42	12	407.6	366.32	396.33	426.35
13	425	366	397	389	399	59	13	395.2	366.32	396.33	426.35
14	372	364	389	401	415	51	14	388.2	366.32	396.33	426.35
15	410	432	405	397	411	35	15	411.0	366.32	396.33	426.35
16	422	393	408	370	386	52	16	395.8	366.32	396.33	426.35
17	376	390	348	409	394	61	17	383.4	366.32	396.33	426.35
18	357	389	412	394	423	66	18	395.0	366.32	396.33	426.35
19	409	408	414	431	388	43	19	410.0	366.32	396.33	426.35
20	375	375	415	417	371	46	20	390.6	366.32	396.33	426.35
A2	=0.58					=51.75		=396.33
FIND: Plot chart
Sample Range = Sample Max - Sample Min
= Average Sample Mean
Upper Control Limit, UCL = + A2 *
Lower Control Limit, LCL = + A2 *

• The A₂ value varies with sample size and comes from a table of control chart parameters. It is named A2_.
• The sample range(=MAX(B4:F4)-MIN(B4:F4)) is in the first calculation column to allow the calculation of (=AVERAGE(G4:G23)). It is named Rbar.
• The sample numbers are repeated to provide the left column, X-axis values, of the chart values.
• The sample averages, (=AVERAGE(B4:F4)), are the points to be plotted.
• (=AVERAGE(I4:I23)) is calculated as the average . It is named Xdblbar.
• The value of LCL (=Xdblbar-A2_*Rbar) is calculated and dragged down to provide the LCL line on the chart.
• The value of (=Xdblbar) is calculated and dragged down to provide the Centerline on the chart.
• The value of UCL (=Xdblbar+A2_*Rbar) is calculated and dragged down to provide the UCL line on the chart.

The chart is a normal XY Chart. The straight lines were formatted to remove the markers and change the colors.

Data labels are added to the last value of the UCL and LCL lines. To do this:

Click the right end of the UCL line. This highlights all the data points. Click again and the last data point is highlighted. Right click and select Format Data Point on the popup menu. On the Format Data Point menu select Show Value. This provides the number. Right Click the number and select Format Data Labels on the popup menu. Select the Number tab on the Format Data Labels menu. Select Custom and format the number as "UCL "0.00. Repeat the process on the LCL line.

Problem set 9

From this data set, create a Histogram and an chart. You should be able to select the table below- copy and paste the values directly into a spresdsheet