X-bar and R Charts on Excel

X-bar and R Charts are used when there are rational subgroups of data.  These instructions are based on an assumption that all subgroups are the same size.  A step by step overview of the process is listed below.

Step 1:    Collect the data and enter it into a spreadsheet.   Put all data from the same subgroup in the same row.  For this example, we will use the following data set:

Step 2:    Calculate X-bar and R for each subgroup.  To do this create a column for the X-bars and a column for the Rs.  I'll use F and G.  For the first subgroup (the data in Row 2, Columns B, C, and D), the formulas will be:

=average(B2:D2)                    to find X-bar
=max(B2:D2)-min(B2:D2)     to find R

Then fill down the columns to find the other X-bars and Rs.  The resulting spread sheet will look like the following:

Step 3:    Make two Run Charts--one with the X-bar data and another with the R data (place the Run Chart with the X-bar data at the top of the page and the Run Chart with the R data at the bottom of the page).  The results should look similar to the following:

Step 4:    Look at the Run Charts.  If there are obvious patterns (trends, zigzag patterns, etc.), look for the cause.  You have enough evidence to react to special cause variation without doing additional arithmetic.  If you do not see obvious patterns, go to Step 5.

NOTE:  Computer output for Steps 5 and 6 is shown below Step 6.

Step 5:    Calculate limits for the R chart and conduct runs tests.  If any of the runs tests are failed, search for the cause of excess variation within subgroups, and take action to remove the cause.  Depending on subject matter knowledge, you may want to return to Step 1 once action has been taken or simply remove the subgroups that were collected while the special cause of variation was present and recalculate limits.   For this example, no points plot outside the limits, no points plot in the upper A zone (and there isn't a lower A zone), the most consecutive points in the C zones is 3, the most consecutive points on the same side of the center line is 3, the most consecutive increases/decreases is 3, and the most consecutive points alternating up/down is 5.  Therefore, none of the runs tests are failed.

Step 6:    Calculate limits for the X-bar chart and conduct runs tests.  If any of the runs tests are failed, search for the additional cause(s) of variation between subgroups.   For this example, there are no points outside the limits, there are no points in either A zone, the most consecutive points in the C zones is 3, the most consecutive points on the same side of the center line is 4, the most consecutive increases/decreases is 2, the most consecutive points alternating up/down is 4.  Therefore, none of the runs tests are failed.

The formulas used to calculate limits for R, the width of the zones, and the height for the zones are given below in Column G along with the same information for the X-bar chart in Column F.

The values shown as a result of using these formulas are:

There are multiple ways to plot the lines on the run chart.  One way is to print the run chart and use a ruler to draw the lines.  Another is to use the drawing functions on Excel (but this can be tricky since it is possible to draw lines that are not attached to the graph and do not move with the graph when it is moved).  Another approach is to create another column of data for each line to be plotted.  Each entry in this column should include the same value.  For example in the illustration below, cells J1:Q8 contain the data for the X-Bar Chart, and cells J10:J17 contain the data for the R Chart.

To plot the X-Bar Chart, select cells J1:Q8 prior to accessing the Chart Wizard.  In Step 2 of the Chart Wizard, be sure to specify that the data are in columns.  Once the graph is produced, a good bit of "clean up" is needed to get rid of the chartjunk produced in the form of different colors for the lines and symbols on the lines (just double click on each line and make the appropriate changes).  

Use a similar approach to plot the R Chart.

The resulting control charts look like the following:

Step 7:  Interpret the results.  This involves estimating process parameters. 
The mean (m) is estimated with the centerline from the X-bar chart.  In this case, 50.
The standard deviation (s) is estimated by calculating R-bar/d₂.   In this case, 9.714/1.693=5.74.
From this we can compute natural process limits of m ± 3s.   In this case, the lower natural process limit (LNPL) is 32.78, and the upper natural process limit (UNPL) is 67.22.  Thus, we can say that we would expect outcomes to range from 32.78 to 67.22 and average 50.

Back to Calculating Control Limits with Excel

Back to Excel Hints Page

Back to BUSA 3110 Home Page