Software : Business : Excel Spreadsheets: Quality Control : Spreadsheet QC

Manufacturer : User Solutions, Inc

Spreadsheet QC is a collection of 15 professional templates for use with Lotus 1-2-3, Microsoft Excel, Quattro Pro, and all compatibles. The goal of Spreadsheet QC is to make statistical process and quality control more accessible by automating the main techniques in a familiar form, the spreadsheet. Spreadsheet QC is ideally suited for small operations which are just beginning to implement quality improvement programs because of its low cost and ease of use. It is also ideally suited for large operations to complement existing Total Quality Management (TQM) systems for flexible, ad-hoc reporting and individual training.

How Does it Work

Spreadsheet QC will help customers maximize their existing investment in software, hardware, and training. Using familiar spreadsheet menus, users simply input the data into spreadsheet cells and immediately obtain information that can be used to pinpoint and solve quality problems. For any data input, the user can enter in a simple number, an equation, enter a link back to another spreadsheet for desired data, or even read data from another system, all from within the familiar spreadsheet environment. The spreadsheet's graphic capability enables quick and easy presentation. Additionally, the actual source code, referred to as "macros", is included with each product, enabling customers to modify the macros to further customize and enhance Spreadsheet QC.



Ready tu buy?

Additional Information: Spreadsheet QC

Requires Excel 97 or later

30-day money back guarantee!

Buy Online

bbuy.gif (1202 bytes)


More Info

The aim of the Spreadsheet Quality Manager (SQM) is to make statistical process and quality control more accessible by automating the main techniques in a familiar form, the electronic spreadsheet.  SQM contains fifteen spreadsheets for probability calculations, data analysis, control-charting, and acceptance sampling.  SQM works with Lotus 1-2-3 (Release 2.01 or later) and compatible software.  The spreadsheets are practical because all examples in SQM are real.  Company names and proprietary data have been disguised but the problems have not.

Today, statistical quality control is generally accepted in manufacturing.  Implementation of statistical quality control in some form is a requirement in any credible Total Quality Management program.  In the service industries, statistical quality control is not yet prevalent, although recent progress has been encouraging.  The aim of the Spreadsheet Quality Manager (SQM) is to make statistical quality control more accessible by automating the main techniques in a familiar form, the electronic spreadsheet.  The techniques can be organized into four categories:  probability distributions, data analysis tools, control charts, and sampling models.

Data analysis tools

Some variation in the measured quality of any product is inevitable.  Four data analysis tools in Chapter 2 assist in identifying, classifying, and explaining this variation.  The simplest model, PARETO, ranks product defects or nonconformities by the frequency of occurrence.  The basic idea is that most defects are due to a relatively small number of causes.  PARETO focuses attention on the vital few causes in order to get the quickest payback on quality control effort.  The second model is HISTOGRA, which produces a frequency distribution, a table showing the number of data observations that fall into specified classes or intervals.  The table is graphed in the form of a histogram, a special type of bar chart, with the heights of bars corresponding to the frequencies in each class.  The table and histogram help determine whether the data have a tendency to cluster around a central value, and they also identify extreme observations.  Third, NORMTEST is used to test whether quality measurements are normally distributed.  This test is a requirement before using I-CHART.  In other situations, the test provides some assurance that future quality measurements will be well-behaved, with a small probability of extreme observations.  Fourth, SCATTER analyzes variation in the relationship between two quality variables.  The relationship is measured by a correlation coefficient, which is automatically tested for statistical significance.The data analysis tools should be used prior to the development of control charts in order to gain an understanding of the data and to correct obvious problems in the production process.  Later, these tools are used to evaluate data when one of the control charts reports a problem.

Control charts

The basic idea in control-charting is to select a sample from a production process at equal intervals of time and record some quality characteristic.  The most common quality characteristic is the mean of each sample.  If the process is under control, the series of sample means should vary about the population mean in a random manner.  That is, we should expect some natural variation in any process, and there should be no assignable cause to this variation.  If the process is in control, almost all sample mean values should fall within control limits, defined as the mean plus or minus 3 standard deviations.  The standard deviation is a measure of the variation of a process.  If all sample observations are constant, the standard deviation is zero;  as variation increases, the standard deviation increases. It is important to understand that the control chart is a management-by-exception tool.  If a sample mean falls outside the control limits, there is a very small probability that this happened due to randomness or chance alone.  In fact, with control limits set at 3 standard deviations, the probability is less than 1% that the sample mean occurred due to chance.  There is a very large probability, more than 99%, that the sample mean is due to an assignable cause and an investigation should be conducted.The control charts in SQM are classified as either variable or attribute charts. Variables are measurements on a continuous scale such as inches or pounds.  What types of variables can be monitored with the variables control charts?  Anything that can be measured and expressed in numbers, such as temperature, dimension, hardness number, tensile strength, weight, viscosity, etc.  Attributes are discrete data such as the number of items in the sample that are defective or the number of defects in one unit of product.  There are five variables control charts.  First, HL-CHART compares the mean and range of each sample to specification limits.  This model also computes process capability indexes to ensure that the production process is actually capable of meeting specifications.  The name HL-CHART comes from the high-low chart used to display the range of each sample.  HL-CHART also prepares data files of means and ranges or means and standard deviations for use in other models.

The second variables control chart, MR-CHART, monitors the mean and range of a series of samples.  The third, MS-CHART, is an alternative to MR-CHART and monitors the mean and standard deviation within each sample.  In both MR- and MS-CHART, the sample size must be greater than one observation.  Finally, in cases where it is not feasible or economical to inspect more than one unit at a time, either I-CHART or MA-CHART is used.  I-CHART monitors the value of individual quality measurements and may be the only alternative when the process being monitored is lengthy and yields only one observation per shift or day.MA-CHART monitors the moving average of individual observations.  In continuous process manufacturing, especially in chemicals, there may be a great deal of variability in individual measures of quality at intermediate stages of the process.  Variability is then reduced by design as the product is mixed or blended in later stages of manufacturing.  To offset this variability at intermediate stages, it is common to smooth the data by taking moving averages.  In any application, moving averages display trends more clearly than the original data.

Two attributes control charts are available, P-CHART and CU-CHART. P-CHART is the most widely used of all control charts because of its simplicity.  It can be applied to any inspection process that has only two results:  accepted or rejected.  The chart tracks the percentage of rejected units in a series of samples.  Occasionally, product classification as merely good or bad is not enough, and variable measurements do not apply.  For example, in evaluating the quality of a new automobile, there could be many defects but it would be misleading to classify the entire automobile as unacceptable.  The solution in situations like this is the CU-CHART, which monitors the number of defects per inspection unit.  In general, the inspection unit is expected to have some defects, and we wish to know whether the number of defects is excessive.

How many samples do you need to start a control chart?  Theory suggests a minimum of 20 to 25 samples in order for the control limits to work as planned.  In this book, some examples are based on a smaller number of samples for the sake of simplicity and to minimize the disk space required to store the worksheets.

Pareto diagrams (PARETO)

The simplest, and perhaps the most powerful, quality model is the Pareto diagram, named after Vilfredo Pareto, an Italian economist who studied the distribution of wealth in the 19th century.  Pareto observed that a large proportion of the wealth was controlled by a small segment of the population.  This Pareto effect also applies to the distribution of quality defects:  a large proportion of defects is almost always due to a small number of problems.  The Pareto diagram focuses attention on these vital few problems to achieve quick quality improvements.

To develop a Pareto diagram, first identify the types of defects generated by a production process.  Second, count the number of times each type occurred.  Finally, graph the results in a histogram depicting both the number and cumulative percentage of defects. 

Frequency distributions and histograms (HISTOGRA)

A frequency distribution is a table that shows the number of data observations that fall into specified classes or intervals.  A histogram is a type of bar chart, with the heights of bars corresponding to the frequencies in each class.  These tools portray the dispersion or spread of quality data, determine whether the data have a tendency to cluster around a central value, and identify extreme observations.

To illustrate, Fort Bend Custom Ammo uses frequency distributions and histograms to analyze the lengths of brass cartridge cases at its Sugar Land, Texas, plant.  Fort Bend purchases once-fired cartridge cases from shooting ranges and military bases.  The cases are inspected, cleaned, deburred, polished, deprimed, resized, and reloaded to specifications furnished by distributors.  One problem in manufacturing 9 millimeter (9MM) ammunition is that brass case lengths vary more in this caliber than in any other.  Excessive case-length variation leads to erratic bullet velocities and poor accuracy.  Cases which are too long can also jam firearms.  Fort Bend's specifications for 9MM cartridge lengths restrict variation to limits of 0.7355 to 0.7555 inches.  The company wants a tight distribution of case lengths within that range, with the mean limited to a more restricted range:  0.7405 to 0.7505 inches.

Control Charts for Individual

The control charts in this chapter monitor one observation at a time rather than groups of observations organized into samples.  These charts are used when inspection and testing are expensive or when the production process is simply too slow to monitor groups of observations.  There are two alternative models:  I-CHART monitors individual observations directly, while MA-CHART monitors a moving average of the observations.

Although the I-CHART is usually identified with manufacturing applications, it is widely used in casinos to monitor card, dice, and roulette games.  The individual observations entered in the chart are typically the net cash profits recorded when a dealer settles his or her account with the house at the end of a work period.

P-CHART is the most versatile and popular control chart.  To use P-CHART, quality inspectors classify sample items into two groups:  good or bad.  This can mean defective or nondefective, conforming or nonconforming to specifications, acceptable or unacceptable, or other definitions in which there are only two categories of results.

Brookshire Cookware Corporation, a producer of pots and pans located in Brookshire, Texas, uses P-CHART worksheets to monitor the quality of component parts received from its Mexican suppliers.  At the end of each business day, Brookshire faxes a copy of each P-CHART to its suppliers.  This feedback helps maintain a good working relationship with suppliers and alerts them to potential problems.

The CU-CHART model monitors the number of defects per inspection unit.  In general, the inspection unit is expected to have some defects, and CU-CHART determines whether the number of defects is excessive.  Three conditions must be satisfied to use CU-CHART.  First, the definition of an inspection unit must be constant from one time period to the next.  Second, there must be a large number of opportunities for defects to occur in each unit produced.  Third, the probability that a defect will occur at any particular location in each unit must be very small and independent of the occurrence of defects at any other location.


Screen for individual values entered  for subgroups( at least one sub group must have been entered ). It is computed " on the fly " and plots the individual values in one of ten bins. The system calculates   minimum  and maximum bin values from the current running mean of entered values  plus and minus  the number of standard deviations( of individual values )  specified on the spreadsheet template ( 3 or more is recommended ) . The standard deviation used is the current running standard deviation for individual values. The system then divides the range specified by the minimum and maximum and plots individual values.


Measures for a business function. The template  subgroups could represent the days of the week for a one month selection or  selected 1/2 hourly output samples from an order entry function etc. etc. The system will compute control limits for the data and allow your improvement team to separate out common cause system factors and special cause factors that impede productivity or cause bottlenecks. The appendix shows an example where the template is used to collect data on daily shipments for a small company.

More Examples of Charts generated by Spreadsheet QC

N 1

N 2

N 3

N 4

N 5

How does it Work


For over 12 years User Solutions, Inc. has been the easy choice for thousands of companies worldwide.

Additional Information: Spreadsheet QC

Requires Excel 97 or later

30-day money back guarantee

Buy Online

bbuy.gif (1202 bytes)


Business Software

MS Excel Add-ins, Spreadsheets, Templates

Copyright © 1996-2005 IovSoft All Rights Reserved