Statistics

Watching my Weight with SPC (Statistical Process Control)

Tuesday, April 28, 2020 by Jon Peltier 8 Comments

Tuesday, April 28, 2020 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2020, All rights reserved.

I’ve been working on a Statistical Process Control project for a client, building a workbook to automate construction of control charts. Years ago I wrote a tutorial called Introducing Control Charts (Run Charts). Many processes, in manufacturing, in business, or in nature, show fluctuations in their outputs. We can use Statistical Process Control (SPC) techniques to monitor these processes and ensure the fluctuations stay within expected limits.

I was looking for data to proof out the tool I was building, and I thought I could use my weight as a decent data set. My wife bought a new digital scale in 2006, and I’ve been weighing myself almost every day since then. And being an Excel jock, I put my measurements into a spreadsheet.

In the chart below, you can see how I fluctuated around 200 lb for over a decade. Then 20 months ago my wife and I joined Weight Watchers, and over the course of 6 or 8 months I lost 40 lb.

I thought looking at the past few months would be a good way to illustrate the use of SPC to track a process. This exercise will construct a series of control charts of this data.

Learning about Statistical Process Control

I first learned about Statistical Process Control as a practitioner and as a trainer, while employed as a scientist/engineer for a large manufacturing corporation. One of the resources we had was a deceptively small book called Understanding Variation: The Key to Managing Chaos by Donald J. Wheeler.

Understanding Variation: The Key to Managing Chaos
by Donald J. Wheeler

There are many other information sources about SPC and control charts. The National Institute of Standards and Technology (NIST) has an online Engineering Statistics Handbook, which has a chapter on Univariate and Multivariate Control Charts. Wikipedia has brief articles with many references covering SPC and Control Charts. And Google shows about 1.2 billion results for SPC and 0.5 billion results for Control Charts.

Getting Started

Prepare the Data

The first step is to identify the data and get it into a form where it can be analyzed. I decided to track from 1-Sept-2019 to 1-Feb-2020. Below is the top of my data worksheet, with a few calculations. The data is in three columns of an Excel Table named Table_1. The first two columns are date and weight, manually entered. The third column is Moving Range (MR), which we will use as a measure of variability in the data. The formula in cell C2 and filled down the Table column is

=IFERROR(ABS([@Weight]-OFFSET([@Weight],-1,0)),NA())

Essentially it determines the absolute value of my change in weight from one day to the next. Any error in the calculation (such as trying to subtract the column header) returns NA(), or the #N/A error.

Weight data and preliminary calculations

I’ve calculated some values in a range beside the table, and I’ll explain them as I go along. The little table below the calculations show the formulas I’ve used. I’ve also named these cells as indicated, to make it easier to use the cells in formulas.

Chart the Data

The next step is to plot the data. I’ve made two charts, one of my weight, the other of the calculated moving range. We look first for any obvious issues in the data, such as the spike late in September. If you look at the data above, apparently I gained 18 lb one day, and lost it the next. A more likely explanation is that I transposed digits in 168 and instead entered 186 in the worksheet. I’ll deal with this data issue soon, but for now I’ll continue with the SPC construction.

I added the calculated items as columns in my Table to make it easier to chart them. Having named the cells, I could use simple formulas in the Table: =Mean in cell D2, =LCL in cell E2, etc.

Data table with calculated items — *click on image to enlarge*

Among my calculations are averages of the weight data (Mean) and of the moving range data (MR Bar). Let’s add these as green horizontal lines to the weight and MR charts for reference.

Compute Limits

So far, so good. Now let’s add a measure of “allowable” or “acceptable” variation. If the process is following statistical rules and its variability follows a normal distribution, we would use multiples of sigma, the standard deviation, to identify limits. According to the definition of a normal distribution, 68.3% of values fall within ±1 standard deviation of the mean, 95.5% fall within ±2 sigma, and 99.7% fall within ±3 sigma of the mean. By convention, 3 sigma is commonly used to identify acceptable variations.

We could measure the sample’s standard deviation (SD) directly, multiply it by 3, and use this to determine our limits. But using moving range is more robust, since outliers and non-normal distributions have a greater effect on sigma than on moving range.

The average moving range, or MR Bar, is used to calculate control limits. Less commonly, the median of the moving range is used to compute these limits.

First we determine MR UCL, which is the Upper Control Limit on the moving range, by multiplying the average moving range by 3.268. This is plotted to the moving range chart as a horizontal orange line (bottom chart below). We would expect 99.7% of our MR values to fall below this limit.

In the same way, we calculate the UCL and LCL (Upper and Lower Control Limits) of our individual data. We multiply MR Bar by 2.67, and add it to or subtract it from the mean to get our limits. These are plotted on our chart of individual values as horizontal orange lines (top chart below). Again, we expect 99.7% of our individuals to fall between these two lines.

IMR Chart = Combined Individuals and Moving Range Charts

These charts of measurements along with means and limits are called Control Charts. The chart of individual values is called an I Chart (no, not “eye chart”), and the moving range chart is the MR Chart. Together they are referred to as an IMR (sometimes ImR) Chart.

Our ±3 SD limits are shown in the dashed red lines below (they are calculated as LCL 2 and UCL 2). They fall pretty far outside the MR-based control limits. All points fall well within the SD-based limits, except for the one obvious outlier.

Standard Deviation and Moving Range based control limits

In fact, because the outlier causes two excessive moving range values, the MR-based limits are also too wide, and would lead us to accept points that would otherwise be out of control.

Clean Up Special Cause Variations

Special and Common Cause Variation

The spike in my weight in September is a “special cause” variation, because it is a one-off problem. Since it is obviously not a valid measurement, we can attribute it to a recording error, and ignore it. We want to remove this value from our moving range calculations, since it resulted in limits which were too wide.

The other variation we see in the timeline is “common cause” variation. It comes from variations in inputs, like exercise, meals, and other factors, which are themselves subject to normal variation.

Clean Up the Data

In my adjusted table below (Table_2), I’ve added two columns. Wt 2 simply repeats the data in Weight, using the Table formula =[@Weight]. I can replace any special cause deviation with =NA() or #N/A in this column. MR 2 uses the same formula as MR, based on the Wt 2 column:

=IFERROR(ABS([@[Wt 2]]-OFFSET([@[Wt 2]],-1,0)),NA())

Where there was one bad weight and two bad moving ranges, we now have #N/A values in the table, which we can ignore in the chart and in our other calculations.

Plot the New Data

When we plot our individual and moving range values, the chart scales now show much narrower ranges, and there are no longer any obvious outliers: there is one high individual value and corresponding moving range in January, a few low weights in November, and a few high weights in December.

Let’s add our means and control limits, and see what we have. The MR chart shows the outlying value in late January, and four more moving range values that are just at the limit. In the individuals chart, the low values are within the limits (“in control”) while the high values we eyeballed before are above the UCL (“out of control”).

When values are out of control, we have to examine the process, to ensure that nothing is wrong with our process, and that nothing has changed. I can actually explain some of the variations. On Thanksgiving, I ran a “Turkey Trot” with my daughter, so for a couple weeks I was running more than my usual 3 miles a day: thus the few low values in November. And of course, the few values of 172 coincide with the Christmas and New Year’s holidays.

Standard Deviation vs Moving Range

Below I’ve plotted the SD-based limits along with the MR-based limits. The limits are much closer to each other and closer to the mean than when the outlier was included in the calculations.

Here I’ve plotted these control limits as calculated with and without the outlier. The outlier had a substantial effect on the limits, especially on the SD limits.

Comparison of moving range based control limits and standard deviation based limits

When the variation fits a normal distribution, the two sets of limits are close together, with the MR-based limits wider sometimes and the SD-based limits wider other times. The larger the data set, the closer they will be.

For the rest of this analysis, I’ll ignore sigma and stick to MR-based calculations.

Highlighting Outliers

Enhanced Data

We can enhance our IMR Chart by highlighting points which are out of control. I’ve added two columns to my table to support this. Wt X has this formula

=IF(OR([@[Wt 2]]<=LCL,[@[Wt 2]]>=UCL),[@[Wt 2]],NA())

which shows the value from Wt 2 if it falls outside the control limits, and #N/A otherwise. MR X has this formula

=IF([@[MR 2]]>=MR_UCL,[@[MR 2]],NA())

which again shows the value from MR 2 if it falls above the control limit, otherwise #N/A.

Highlighting the Chart

I’ve added these columns to my IMR Chart as red/orange markers.

Additional Control Chart Rules

There are other features of control charts that indicate a process which is out of control. These are conditions which are not expected to be found in about 99.7% of cases. Here are a handful of common out-of-control rules; the first one is the one I highlighted above.

One point beyond 3-sigma control limits
2 of 3 points outside 2-sigma on same side of mean
4 of 5 points outside 1-sigma on same side of mean
8 consecutive points outside 1-sigma on both sides of mean
15 consecutive points inside 1-sigma on both sides of mean
9 consecutive points on same side of mean
6 consecutive points moving in same direction
14 consecutive points alternating up and down

Advanced SPC software highlights any of these situations, in addition to the 3-sigma violations.

Extending the Data

To show how to manage a growing data set, I added ten more weeks of my weight tracking.

Frozen Control Limits

Typically, when a process is determined to be steady, the limits are calculated and frozen, then these are extended forward. This is illustrated below: the frozen limits were calculated from September through February, indicated with solid lines, and extended into April, shown with dashed lines.

Where I had a few values above the UCL in December and January, I now had several below the LCL and only a few above the mean in February and beyond.

This is evidence of a process shift. Several of the additional rules mentioned at the end of the last section would have been triggered. Checking my exercise records gives us an explanation. For much of the period from September through January, I was running 3 miles a day, four or five days a week. The weather in February was rather mild, so I increased my mileage to about 3.5 miles a day, six days a week.

Moving (Variable) Limits

The control charts below show control limits calculated over the entire range. The process change is still noticeable, but it’s not as clear as with the frozen and extended limits above.

Another problem with continually recalculating limits is that the limits move over time. Points which were in control at one time may be pushed out of control by later measurements. A December point at 170 which was in control when the limits were frozen is now out of control under the newly computed limits.

Staged Analysis

We can overcome this concern by staging our analysis, that is, computing different limits for different subsets of our data. In my latest Table below, I’ve added a column named Stage, which contains 1 for the first stage and 2 for the second; these can be entered manually or with a formula, which for example increments the stage number on a given date. The control limits are computed separately for different stages.

The IMR Chart below shows a staged analysis. Stage 1 looks familiar; the UCL for both MR and Individuals are slightly lower because the large MR late in January coincided with the process change. The violations in stage 1 are the same as before; the few outliers in stage 2 would have been well within the stage 1 limits, but are actually above the stage 2 UCL.

It’s common practice not to compute a separate average moving range for all stages, especially if the stages have small numbers of points, but instead use an overall MR Bar. The chart below uses this combined measure of variation. Stage 1’s control limits are now a bit tighter, so the low weights measured during the Turkey Trot training in November are now outliers. Conversely, Stage 2’s control limits are slightly wider, so there are no outliers in Stage 2.

Posted: Tuesday, April 28th, 2020 under SPC.
Tags: control chart, Statistical Process Control, Statistics.
Comments: 8

Trendline Calculator for Multiple Series

Tuesday, February 12, 2019 by Jon Peltier 13 Comments

Tuesday, February 12, 2019 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2020, All rights reserved.

A couple months back I wrote Add One Trendline for Multiple Series which shows how to add a trendline to a chart, and have the trendline calculated for multiple series in the chart. In fact, that tutorial was based on my answer to a question on Quora, How can I have multiple scatter plots and one trendline for all of them combined in Excel? Some Quora questions can be kind of lame, but this was a good one, especially if I’m getting a second blog post out of it.

Feedback on that tutorial was positive, but it seems that people would like the process to be faster and simpler. Fair enough.

So I decided to write a small add-in that automates the process.

The Manual Process

If you recall, the original problem was that we had three series of data in the chart, and we can easily get a trendline for any or all individual series, but we want a trendline that covers all points in all three series. You can download a workbook with my dummy data and charts here: MultiScatterTrendlineData.xlsx.

Here is the original chart from the earlier tutorial:

And here is the chart with a trendline for each individual series:

Multiple XY Series and Trendlines in One Chart

We created a new series in the chart that included all points from the first three series (the yellow markers cover the blue, orange, and green ones):

Multiple XY Series, Including the Combined Series, in One Chart

This was the tedious step, adding all the data to a new series, and this is the part that my add-in will speed through.

Then we hid the new series by formatting it without markers, and added a trendline:

Multiple XY Series with One Combined Trendline in One Chart

The VBA Code

The three original series in the chart have these formulas:

=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$11,Sheet1!$C$3:$C$11,1)
=SERIES(Sheet1!$E$2,Sheet1!$D$3:$D$11,Sheet1!$E$3:$E$11,2)
=SERIES(Sheet1!$G$2,Sheet1!$F$3:$F$11,Sheet1!$G$3:$G$11,3)

Remember, a series formula has four arguments (a bubble chart series has a fifth argument, but we’ll ignore bubble charts here):

=SERIES(Series Name, X Values, Y Values, Plot Order)

We’ll give our added series a new name, “Combined”, and it will automatically be 4th in the plot order. In between we will combine the X values and Y values of the original three series. Our constructed series formula looks like:

=SERIES("Combined",
    (Sheet1!$B$3:$B$11,Sheet1!$D$3:$D$11,Sheet1!$F$3:$F$11),
    (Sheet1!$C$3:$C$11,Sheet1!$E$3:$E$11,Sheet1!$G$3:$G$11),
    4)

The multiple X value ranges are enclosed in parentheses, as are the multiple Y value ranges.

What our code will do is count the series in the chart, read each series formula in turn, split out its arguments, and concatenate the separate X and Y values into combined X and Y values. The code will then add the new series, apply the arguments of the series formula, hide the markers, and add a trendline.

Here is the simple procedure:

Sub ComputeMultipleTrendline()
  If Not ActiveChart Is Nothing Then
    With ActiveChart
      Dim ixSeries As Long
      For ixSeries = 1 To .SeriesCollection.Count
        Dim SeriesFormula As String
        SeriesFormula = ActiveChart.SeriesCollection(ixSeries).Formula
        SeriesFormula = Mid$(SeriesFormula, InStr(SeriesFormula, "(") + 1)
        SeriesFormula = Left$(SeriesFormula, Len(SeriesFormula) - 1)

        Dim SeriesArgs As Variant
        SeriesArgs = Split(SeriesFormula, ",")

        Dim XAddress As String, YAddress As String
        XAddress = XAddress & SeriesArgs(LBound(SeriesArgs) + 1) & ","
        YAddress = YAddress & SeriesArgs(LBound(SeriesArgs) + 2) & ","
      Next

      XAddress = "=(" & Left$(XAddress, Len(XAddress) - 1) & ")"
      YAddress = "=(" & Left$(YAddress, Len(YAddress) - 1) & ")"

      With ActiveChart.SeriesCollection.NewSeries
        .Name = "Combined"
        .XValues = XAddress
        .Values = YAddress
        .Format.Line.Visible = False
        .MarkerStyle = xlMarkerStyleNone
        With .Trendlines.Add.Format.Line
          .DashStyle = msoLineSolid
          .ForeColor.ObjectThemeColor = msoThemeColorText1
          .ForeColor.Brightness = 0
        End With
      End With
    End With
  End If
End Sub

If you want to run this code, open the VB Editor (easiest way: use the Alt+F11 shortcut), find your workbook in the Project Explorer, and insert a fresh module (Insert menu > Module, or simply Alt+N+M).

If the new module doesn’t say Option Explicit at the top, type it yourself, then go to the Tools menu > Options, and on the Editor tab of the dialog, check the box labeled Require Variable Declaration, and you may as well uncheck the box for Auto Syntax Check. I discuss why in a decade-old tutorial, VB Editor Settings.

Skip a line after Option Explicit in your brand new code module, then copy the code from above, and paste it into the module.

Before you run the code, select a chart. Then press Alt+F8 to open the Macros dialog. Select ComputeMultipleTrendline and click Run. In the blink of an eye, the new series is added, though it’s not visible, and the trendline appears. I used a solid black line, rather than the default dotted line Excel uses, because I think a solid line makes it easier to see.

It doesn’t matter if all series use the same or different X values; the code doesn’t even compare the X values of the different series, it just puts them all into the series formula.

The Multi Scatter Trendline Calculator

I used the code above as the basis for my add-in. I added a custom ribbon tab named Multi Trendline with a custom button labeled Multi Scatter Trendline to invoke the code. I also designed a dialog so that you can select which series in the chart to include in your analysis (and which to exclude).

Preparing to Install the Add-In

You can download the add-in from this link: MultiScatterTrendlineCalculator.xlam. The add-in is packaged in a zip file. Unzip the file, and store the add-in in the User Add-in Library, which is

C:\Users\USERNAME\AppData\Roaming\Microsoft\AddIns\

where USERNAME is your Windows login. You can get there quickly by pressing Win+R (Win = Windows key), typing %appdata% in the Run box, and clicking OK, which opens the Roaming directory, and drilling down to Microsoft and then AddIns.

You can actually store the add-in in almost any convenient folder, but when you use the Add-In Library, it’s easy to find the add-in from within Excel when you install it.

Windows protects your computer from malicious software that came from a different computer than yours, but it also protects your computer from useful software that came from my computer, so you need to unblock the add-in. Right click on the add-in file in Windows Explorer, and choose Properties. At the bottom of the General tab of the Properties dialog, there may be a notice that the file may be blocked, and there is a checkbox to unblock the file.

Check the box, and click OK.

Installing the Add-In

If you have the Developer tab showing on Excel’s ribbon, go there and click on Excel Add-Ins (or if it’s an older version of Excel that has no Excel Add-Ins button, click on Add-Ins) to open the Add-Ins dialog.

Otherwise, click on the File tab > Options > Add-Ins. Click the Go button near the bottom of the list to open the Add-Ins dialog.

Or you can use the old Excel 2003 shortcut, Alt+T+I to open the Add-Ins dialog.

If you stored the add-in in the User Library, it will appear in the Add-Ins dialog as MultiScatter Trendline Calculator. Otherwise you will have to click Browse, then navigate to find the add-in.

Check the box in front of this entry, then click OK, and the add-in is installed, available whenever you run Excel.

If you don’t want the add-in installed all the time, you can simply start it when you need it, using File > Open in Excel, double clicking in Windows Explorer, or dragging it from Windows Explorer and dropping it on Excel.

Using the Add-In

Select a chart, then click the button on the custom ribbon tab.

Multi Scatter Trendline Button on the Ribbon

The dialog pops up. Select which series you want to include, and click OK.

Multiple Series Trendline Calculator Dialog

The series is added invisibly, and a trendline is added using Excel’s default settings. You can format this just like any other trendline, to change the fitting model used, to show the trendline formula on the chart, or to change the trendline’s formatting.

Multiple Series Trendline Added to Chart

About the Add-In

I have left the add-in unprotected in case you want to see how it all works. There is XML code that handles the custom ribbon tab and button. There is code in a second module to handle clicks from the ribbon button. A UserForm (i.e., a dialog) has been added to get input from the user. The main procedure is more detailed than shown in this article, to accommodate this dialog, and to compile data selectively.

I enjoy doing this kind of project. Even with the ribbon components and the dialog, it only takes a few hours. If you need something like this done, send me your requirements and I’ll generate a quote.

I liked this little utility so much that I’ve added it to Jon’s Toolbox, which is a nifty set of tools for quickly manipulating charts and data for such purposes as teaching and preparation for publishing.

Trendline Articles on this Web Site

Posted: Tuesday, February 12th, 2019 under Utilities.
Tags: Statistics, VBA.
Comments: 13

Add One Trendline for Multiple Series

Wednesday, December 5, 2018 by Jon Peltier 22 Comments

Wednesday, December 5, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2020, All rights reserved.

A member of the online forum Quora asked, How can I have multiple scatter plots and one trendline for all of them combined in Excel? I interpreted this to mean, “I have multiple scatter series in my chart, how do I get a trendline for the combined data in the chart?”

I made up some dummy data, and generated this XY chart.

You can download a workbook with my dummy data and charts here: MultiScatterTrendlineData.xlsx.

Of course you can add a trendline to each of the series, and you get this cluttered mess. It has multiple trendlines, not a multiple-trendline.

Note: In a recent version of Excel (I don’t recall if it was 2013 or 2007), trendlines changed from black to the color of the points, which was good for visibility, and they also became a dotted line, which was bad for visibility. I always change to a solid line.

Well, this is not what the person wanted to know. But it’s no big deal to get what they wanted.

First, let’s just check out the chart. The three series have these formulas:

=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$11,Sheet1!$C$3:$C$11,1) =SERIES(Sheet1!$E$2,Sheet1!$D$3:$D$11,Sheet1!$E$3:$E$11,2) =SERIES(Sheet1!$G$2,Sheet1!$F$3:$F$11,Sheet1!$G$3:$G$11,3)

I’ve color coded the formula arguments to show series names in red, series X values in purple, and series Y values in blue (the colors of the series highlights in the worksheet). The numbers at the end are the plot order of each series.

What we need to do is add a series to the chart that uses all of these X values and all of these Y values. There are at least two ways to get this series.

Select Data

Right click on the chart and click on Select Data from the pop up menu.

The Select Data Source dialog appears.

Click the Add button, and the Edit Series dialog appears.

Click in the Series Name box, and add a descriptive label. “Combined” works.

Click in the Series X Values box, then with the mouse select the first range of X values. Type a comma, and select the second range of X values. Type another comma, and select the third range. Note: instead of typing a comma, you could hold the Ctrl key while selecting the second and third ranges, but I find that I make fewer errors if I type the comma and then don’t have to worry about how many buttons I’m holding with how many fingers.

Click in the Series Y Values box, delete the “={1}” that is there, then with the mouse select the first range of Y values. Type a comma, and select the second range of Y values. Type another comma, and select the third range.

The populated Edit Series dialog looks like this:

The Series X Values and Series Y Values boxes contain

=Sheet1!$B$3:$B$11,Sheet1!$D$3:$D$11,Sheet1!$F$3:$F$11

and

=Sheet1!$C$3:$C$11,Sheet1!$E$3:$E$11,Sheet1!$G$3:$G$11

Excel will put parentheses around these comma-separated addresses, and double quotes around the Series Name.

Click OK twice, and a new series will appear, whose markers obscure those of the existing three series.

The formula for the added series is:

=SERIES("Combined",(Sheet1!$B$3:$B$11,Sheet1!$D$3:$D$11,Sheet1!$F$3:$F$11), (Sheet1!$C$3:$C$11,Sheet1!$E$3:$E$11,Sheet1!$G$3:$G$11),4)

We can see that it includes the X values and the Y values for the three original series.

Add Series Formula Directly

You don’t need to use the Select Data Source dialog to add data to a chart. If you select the chart area (just the outermost rectangle containing the chart), you can click in the Formula bar, and enter your formula. Note that you have to enter double quotes around the series name and parentheses around the multiple areas of the X and Y values. You can type the addresses of the individual names, which is inconvenient since you have to remember to include the sheet name and exclamation point; it’s easier to select the ranges with the mouse. You can hold Ctrl while selecting multiple areas, but I find it easier to type a comma between range addresses. Don’t forget to end the formula with a comma, the Plot Order 4, and the closing parenthesis.

When you press Enter, the chart has a new series that hides the old series, just like above.

Add Trendline

Select this new series, click on the plus “skittle” next to the chart, and check Trendline.

Now format the “Combined” series to hide it (no lines or markers), and format the trendline to enhance visibility and to display the trendline formula and R² value.

Update: Trendline Calculator for Multiple Series

For anyone who wants to apply this technique but finds it tedious to add a series with all the X and Y ranges, I’ve built a small utility that does the heavy lifting for you.

It’s a simple Excel add-in that lets you select a chart, choose which series in the chart to include (and exclude), and it builds a hidden series with a visible trendline combining all series that you’ve selected.

In the article I show some simple code that compiles the data into a new series. The add-in is free and unlocked, so you can look into its inner workings: the code, the dialog (UserForm), and the custom ribbon elements.

See Trendline Calculator for Multiple Series for information.

Trendline Articles on this Blog

Posted: Wednesday, December 5th, 2018 under Data Techniques.
Tags: Statistics, Trendlines.
Comments: 22

A Comparison of Peltier Tech and Excel Box Plots

Monday, June 26, 2017 by Jon Peltier 7 Comments

Monday, June 26, 2017 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2020, All rights reserved.

Box and Whisker Charts in Microsoft Excel

A customer asked me to compare Excel Box Plots between Peltier Tech Charts for Excel 3.0, a commercial VBA add-in for Excel, and the new Microsoft box plots introduced in Excel 2016. My email reply has developed into this full article.

Box and Whisker Charts (or Box Plots) were introduced by John Tukey in 1969, as a quick and easy way to visualize the distribution of a set of data by hand. Computers were not yet widely available, and box plots make use of readily determined medians and quartiles, rather than computationally intensive means and standard deviations.

Over at Flowing Data, Nathan Yau has a short tutorial showing How to Read and Use a Box-and-Whisker Plot.

I’ve written a popular tutorial that shows how to construct Excel Box and Whisker Diagrams (Box Plots). I’ve also built Peltier Tech Charts for Excel 3.0, a commercial Excel software package (aka “add-in”) that includes Excel box plots among its many features.

Microsoft introduced several new chart types in Excel 2016, the first new charts since Excel 97. Among these are Box Plots, Waterfall Charts, and Histograms, which I guess makes Microsoft my biggest competitor in the Excel Charts space.

Peltier Tech Box Plots

History

I’ve been using box plots since grad school in the mid 1980s, and probably earlier. While working as a research scientist, I used Minitab for much of my analysis, but it was awkward to use and I hated the charts. It was easy enough to do what I needed in Excel, except for box plots. Box plots sucked in Minitab too, and eventually I worked out the basics of Excel box plots, in the mid 1990s. This required extensive tables of calculations as well as tricky combinations of chart types, but I guess that ‘s what I do.

In the early 2000s I started developing VBA procedures to build Excel box plots, to take away the tedium and pain of compiling the calculations and building the charts by hand. Key milestones after this include:

September 9, 2006: I announced a demo of a New Charting Utility – Box And Whisker Charts. I hadn’t started my own blog yet, that didn’t happen until March 2008, so I posted this on Dick Kusleika‘s Daily Dose of Excel blog. I received a lot of feedback, and made many incremental improvements and fixed many annoying issues.
October 30, 2007: I announced (again at Daily Dose) the Updated Box And Whisker Chart Utility. This Excel VBA add-in included new features and improved old features, and most of the bugs went away.
September 1, 2008: I launched my first software product, the Peltier Tech Waterfall Chart Utility 1.0.
December 3, 2008: I sold the first license of the new Peltier Tech Box and Whisker Utility 1.0.
April 14, 2013: I released the Peltier Tech Chart Utility 2.0. This new utility bundled together my separate first-generation chart utilities, including the Waterfall and Box Plot Utilities mentioned above, and also included a lot of chart-related features, for modifying chart data, formatting charts, and exporting charts. Eventually there were Standard and Advanced editions of this utility, as well as Windows and Mac versions.
September 18, 2015: I released Peltier Tech Charts for Excel 3.0. This included more charts, including a new Grouped Box Plot in the Advanced Edition, and new features. It serviced Windows and Mac with a single add-in, but maintained Standard and Advanced editions.

Output

It’s easy to build Excel box plots using Peltier Tech Charts for Excel 3.0. I’ll use this sample data range: there are three subsets of data (“A”, “B”, and “C”) aligned in columns, with the data subsets labeled in the first column.

Select the data you want to use and click the Box Plot button on the Peltier Tech tab in Excel’s ribbon. If you only select one cell within the data range you want to use, the routine finds the entire block of data that contains the active cell. The routine guesses whether the data is in columns or rows, whether there are data labels in the first row or column, and whether there are record identifiers in the first column or row. The data range and various row and column settings are prepopulated in the dialog.

When the dialog opens, you can adjust the data, if necessary. You can also select various style components, such as whether the chart is vertically or horizontally oriented, whether you want the box and whisker style or a four-box style that replaces the whiskers with another set of boxes,
and whether you want to display outliers. These orientation and style settings are remembered for next time you run the program.

The program inserts a new worksheet. The chart appears at the top, with a block of formulas below that, where you can see calculated statistics (count, min, max, mean, standard deviation, median, and quartiles). There are intermediate calculations below this, and the bottom of the worksheet’s used range is populated with the input data (linked to the original data). Calculations that facilitate display of outliers also appear at the bottom of the used range.

The chart is sized according to the number of categories portrayed. Vertical charts are also aligned with the columns of data in the worksheet below, to make it easier to visually pick off statistics from the worksheet formulas.

To the right of the chart are controls that let you adjust various settings. For example, you can choose to show or hide outliers and means, and you can select the method used to calculate quartiles.

The chart is a regular Excel chart, albeit with some customized formatting do get the appropriate box and whisker appearance. You can copy it and paste it anywhere, such as another worksheet or another application (a presentation in PowerPoint or a report in Word). You can also modify the size and formats of the chart, being careful not to mess up any of the custom formatting that make it work as a box plot.

Microsoft Box Plots

Making native Excel box plots in Excel 2016 is easy, and perhaps faster than using the Peltier Tech tool because there is no dialog and fewer options. Select the data, go to the Insert tab of Excel’s ribbon, find the Chart group in the middle of this tab, click on the Insert Statistic Chart dropdown button in the middle of this group . . .

. . . and click the Box and Whisker button. It seems like I described a lot of steps, but it’s really quick once you know where the buttons are.

Excel inserts a box plot of the default chart size (in the US it’s 3 by 5 inches) in the default position (the middle of the active window). When the chart is selected (any chart element), the input data that went into the analysis is highlighted.

Here is an Excel box plot without the added distraction of data or selection highlights.

Not too hard, not too bad looking. But these charts have issues that I would like to address, and unfortunately I can’t address them all.

Comparisons between Microsoft’s and Peltier Tech’s box plots will be discussed in line.

Issues With Formatting

Like all new charts introduced in Excel 2016. Microsoft’s box plots are built using a new charting platform (or engine). This new platform will eventually make it easier for Microsoft to give us new chart types and new features, and eventually all the existing charts will move to this new charting platform. Unfortunately, for the time being, not all of the features we’ve had in the existing chart engine are present in the new one. This means that some things just aren’t available. Not yet, anyway, although Microsoft assures us that the new charts will be brought to parity with the old.

For example, in the new box plots, we can set our vertical axis minimum and maximum scale values, but we can’t change the spacing between tick marks and labels, nor can we set the position of the labels (i.e., low, high, next to axis, or none).

We also have little control over the position and size of many chart elements in the new charts. We cannot resize or reposition the plot area, the chart title, data labels, or the legend, other than choosing between some preset options for some elements (such as label position above or below points; or legend at top, bottom, left, or right).

There also is a disconnect between what you can do manually with a new chart, and what you can do with VBA. Recording a macro leaves out some actions (for example changing the fill color of the boxes), and when you type them in, you get run time errors.

Some of the formatting issues are related to the new chart platform, which is still being developed, some may be due to shortcomings in the way the box plot is implemented, and some are issues with all charts in Excel.

As with any Excel chart, you format an element by selecting it and pressing Ctrl+1, or right-clicking on it and selecting the Format item at the bottom of the pop-up menu. Here is a box plot with a series selected, showing the Format Series task pane.

Overlap and Gap Width

The first thing I noticed is that the default box plot has wide spaces on either side of cluster of stacks (below left). The data is only given the middle half of the plot area, and a quarter of the plot area on either side is blank; at the same time, the space between adjacent boxes is very small. That’s equivalent to a column chart with series overlap of -11% and gap width of 330% (below right). Inconsistently, the box plot has a stated gap width of 100%, and no reported series overlap.

If I had a regular column chart that looked like the box plot, I’d reduce the gap width and increase the overlap. Here are two appropriate combinations:

I can decrease gap width in the box plot, but there is no way to access the series overlap, so can’t increase space between colored stacks of boxes. The best I can do is something like the box plot below left, equivalent to -10% overlap and 105% gap width in the column chart, below right. The bars are still not evenly distributed across the box plot.

Microsoft treats each column of raw data as a series, which does make some kind of logical sense. The output related to that series has unique formatting, resulting in this example in data plotted with blue, orange, and gray pixels. The box plot has no gap width parameter, though, so the spacing between different colored elements is not adjustable.

Peltier Tech treats the charted data differently, so the upper boxes comprise one series and the lower boxes another. This means they are not by default formatted differently, and only gap width is needed to control this spacing. Below is the default Peltier Tech box plot, which is equivalent to a column chart with 100% gap width; the data is still evenly spaced across the chart.

Changing gap width to 50% in either chart type results in data which is still evenly spaced across the chart (bottom row).

Category (Horizontal or X) Axis

Another effect of Microsoft’s assigning a series to each column of input data is that there is only one category. This results in the “1” label below the plot area. This label is kind of useless: why not use the axis to label the data being plotted?

For example, look how the Peltier Tech chart has “A”, “B”, and “C” labels along the axis. And with the axis labeled clearly like this, there is no need to color-code the boxes.

This is really just the difference in a regular column chart between plotting a simple data set by column or by row. In the chart below left, the labels “A”, “B”, and “C” are series names, and only appear in the legend. Conversely, in the chart below right, the labels are categories, and appear along the category axis (X axis).

The chart below left requires color coding and a legend to identify the different bars in the chart, while the chart below right has no need for potentially distracting colors, since the bars are clearly identified by the axis labels.

Okay, so Excel gives us an unhelpful axis label. Let’s just replace it with a legend (below left). Then let’s hide the axis label. Hmmm, there’s no Label Position option in the Format Axis pane. That’s inconvenient.

Let’s apply a custom number format of ” ” (double quote, space, double quote), which will force Excel to format the axis label as an unseen space character. Unfortunately, this leaves a large blank space between the axis and the legend.

Okay, then let’s stretch the plot area. Aaargh! (No, it’s not Talk Like a Pirate Day, it’s Using Excel Everyday.) The plot area cannot be resized or moved in a new platform chart.

So let’s try formatting the legend. What if I uncheck the “Show legend without overlapping chart” box? Finally! We have the chart on the right.

In an old school Excel chart I’d also stretch the legend widthwise, to roughly align the legend entries under the boxes, but the new chart platform doesn’t let me resize the legend, or even drag the legend to center it under all the data.

Box Fill Colors

When dataviz experts talk about using colors, they’ll tell you that you should use darker shades for lines and text, and lighter shades for fills. A problem with Excel charts in general is that they use the same medium shades for everything. The lines are okay, but sometimes text is too light, and usually fills are too dark.

It’s not usually a question of legibility, it’s just that large saturated fills overwhelm the reader. However, the box plot uses a slightly darker shade of the fill color for outlines, whiskers, and the “x” marker that indicates the mean. It’s not bad for most of the standard colors, but for the blue series in particular, I find it difficult to resolve the median and mean (below left).

You can easily enough format each series, and choose a lighter shade of each color for the fills, and everything shows up just fine (below right)

In an old school Excel chart, I’d replace the “x” marker with a more visible marker, like the diamond used in the Peltier Tech box plot. But the Microsoft box plot doesn’t have that flexibility.

Adding Features to the Chart

In addition to “How can I make Excel box plots?”, many of the articles on this blog answer questions like “How do I add a line to my bar chart?” or “How can I shade a target region in my chart?”. Most of the answers require you to add data to the chart, assign a particular chart type to the added data, and apply special formatting (including hiding) and labels to this new data. In a Peltier Tech box plot, for example, I can add lines to indicate high and low targets (below left) or add a shaded area to show a favored region (below right).

I’ve tried this with the Microsoft box plot, with no success. If I copy data, I cannot use Paste or Paste Special to get the copied data into the chart. If I add data using the Select Data button, I can add data, but Excel assumes it has to construct another box and whisker series. When I go to the Change Chart Type dialog, Combo Chart is disabled, so I can only change the type of the entire chart. Maybe Microsoft will enable combining other chart types with a box plot, but I suspect it will be like the bubble chart, which cannot have other chart types mixed in.

Concerns About Data or Statistics

Exclusive or Inclusive Calculations

Exclusive and Inclusive refer to how quartiles are calculated when the data set isn’t divisible by 4. If we have five values, the median is the third value, because it’s in the middle. The first quartile is the median of the bottom half and the third quartile is the median of the top half. Exclusive calculations of our quartiles exclude the third value, and compute the medians of the first and second values and of the fourth and fifth. Inclusive calculations of our quartiles include the third value, and compute the medians of the first through third values and of the third through fifth. Exclusive calculations result in a wider interquartile range and fewer outliers than inclusive calculations.

Tukey’s “hinge” method is an inclusive point-counting method to find quartiles, and Excel classically uses an inclusive N-1 interpolation method to compute quartiles. The Moore-McCabe hinge (point-counting) method is exclusive, as is Minitab’s N+1 interpolation method. Statistics packages impose a preferred method or allow the user to select one. Peltier Tech offers a selection of methods, and Microsoft offers two: exclusive and inclusive.

Despite Excel’s historical use of inclusive interpolation, Microsoft’s box plot uses exclusive calculation by default. Peltier Tech uses inclusive by default. Note the differences in the box heights, whisker lengths, and number of outliers in the two charts below:

The plots are the same if both are using inclusive calculations. . .

. . . or if both are using exclusive calculations.

Because Microsoft applies exclusive or inclusive calculation on a per-series basis, it’s possible to have some data in the chart plotted on an exclusive basis and other data on an inclusive basis. Peltier Tech applies the calculation method for all data in the chart.

This is dangerously confusing, because it displays inconsistently computed data on the same chart. Except for the identical medians and means in the chart above, the two sets of data look quite different.

There is an exhaustive comparison of various quartile calculation methods in Quartiles for Box Plots on this blog.

Outliers or No Outliers

In a box plot, you have the choice between simple box and whiskers, where the boxes show the interior two quartiles, and the markers show the first quartile down to the data minimum and the fourth quartile up to the data maximum.

A point is an outlier if it is more than 1.5 times the interquartile range from the interior quartile boxes. (The interquartile range, or IQR, is the third quartile value minus the first quartile value.) When showing outliers, the whiskers don’t reach out to 1.5 IQR beyond the interior quartile boxes, they reach out to the fences, which are the furthest points which are not outliers.

In the top two charts below, we see that formatting the Microsoft box plot to hide outliers has no effect on the whiskers: they still show the distance to the upper and lower fences, not to the maximum and minimum values. This distorts the data by not including all of the data in the display.

The bottom two charts show that Peltier Tech box plots jump out to the extrema when the outliers are hidden.

Notice that the Peltier Tech outliers have two different styles. It is customary though not mandatory to use a star (asterix) to indicate an outlier that lies between 1.5 and 3.0 IQR from the interior quartile boxes, and to use an open circle to indicate an outlier that lies beyond 3.0 IQR from the boxes. Microsoft only shows one class of outliers.

Tabulated Statistics

Peltier Tech inserts a new worksheet with its box plot and all of the related statistical calculations. Microsoft doesn’t provide this table, but if you add data labels to the box plot, the data label value option labels the values of everything: the mean and median, first and third quarter, whisker endpoints, and outlier values. It can be quite messy (below left). Even if you don’t have these labels, if you mouse over any of these values on the chart, it shows in the ChartTip (below right). If you need a table, you will have to write your own formulas, or do a lot of mousing over the chart and typing of values.

Summary

After two decades of the same chart type choices, Microsoft has started introducing new options. It has been possible to make these non-native charts in Excel, using special data layouts and custom combinations of chart type and creative formatting. The new built-in charts are easier and faster than existing manual techniques, but these are still not as capable as programmatic options, available as Excel add-ins powered by VBA.

The Peltier Tech box plot is more flexible than the Microsoft box plot, introduced in Excel 2016, especially in terms of user formatting of the produced chart. The Peltier Tech waterfall chart has also been shown to be superior to Microsoft ‘s waterfall, with more options for labeling and other formatting, and more options for the form of the output (stacked waterfalls, rotated waterfalls, etc.).

Peltier Tech Charts for Excel 3.0 includes box plots, waterfall charts, and a variety of other charts and formatting tools developed to fill gaps in Microsoft’s charting options.

Posted: Monday, June 26th, 2017 under Chart Types.
Tags: Box and Whisker Chart Utility, Box Plot, Statistics.
Comments: 7

Excel Box and Whisker Diagrams (Box Plots)

Tuesday, June 7, 2011 by Jon Peltier 191 Comments

Box and Whisker Charts (Box Plots) are commonly used in the display of statistical analyses. Microsoft Excel does not have a built in Box and Whisker chart type*, but you can create your own custom Box and Whisker charts, using stacked bar or column charts and error bars. This tutorial shows how to make box plots, in vertical or horizontal orientations, in all modern versions of Excel.

In its simplest form, the box and whisker diagram has a box showing the range from first to third quartiles, and the median divides this large box, the “interquartile range”, into two boxes, for the second and third quartiles. The whiskers span the first quartile, from the second quartile box down to the minimum, and the fourth quartile, from the third quartile box up to the maximum.

* Note: Microsoft Excel 2016 added a box and whisker chart, but it is not as flexible as those created using this protocol.

Sample Data and Calculations

To play along at home in Excel 2007 or 2010, download the workbook Excel_2007_Box_Plot_Workbook.xlsx.

Let’s use the following simple data set for our tutorial. The values were taken from a normally distributed population with a mean of 10 and standard deviation of 5. There are four sets of 20 values.

All of these values are positive. If your data set has mixed positive and negative values, this technique requires major modifications.

First, insert a bunch of blank rows, and set up a range for calculations. Only the horizontal version of the box plot uses the last calculated row, “Offset”. It will not hurt to include it in the vertical box plot’s calculations.

First, compute some simple statistics, such as the count, mean, and standard deviation. The formulas used in column B are shown in column G of the screen shot.

Now let’s compute the minimum and maximum, median, and first and third quartiles.

Finally, let’s determine which values we need to plot. Our chart has a box for the second quartile, which shows the difference between median and first quartile calculated above. It has a box for third quartile, which show the difference between the third quartile calculation and the median. The bottom of the lower box rests on the first calculated quartile. The down whisker is as long as the first quartile minus the minimum, and the up whisker is as long as the maximum minus the third quartile.

The offset values are calculated as follows: In my example, I have four categories, Alpha through Delta. I can divide my horizontal chart into four horizontal strips, numbered from 0 to 4, each containing one box-and-whisker unit. I need to position my average points in the middle of each 1-unit horizontal strip. These will ultimately go onto a secondary vertical axis which I will have conveniently scaled from 0 to 4. Hence the Y values I will need are 0.5, 1.5, 2.5, and 3.5.

Chart Construction

Select the header row of the calculated data, then hold Ctrl while selecting the three rows that include Bottom, 2Q Box, and 3Q Box. This multiple-area range is highlighted in orange below.

With this range selected, insert a stacked column chart or a stacked bar chart. Be sure to use the stacked version, and not the 100% stacked version, of the column or bar chart.

The labels in the bar chart go bottom-to-top. To reverse the labels, select the vertical axis, press Ctrl-1 (numeral one) to open the Format Axis dialog, then check the “Categories in Reverse Order” box, then under “Horizontal Axis Crosses”, select “At maximum category”.

To add the down whisker, select the Bottom series, then in the Chart Tools > Layout tab, click Error Bars, and select More Error Bar Options from the bottom of the menu. Choose the Minus direction, select Custom for Error Amount, and click on Specify Value. Leave the contents of the Positive Error Value box alone (“={1}”) in the mini dialog that appears, then clear the Negative Error Value box and select the Whisker- row from the table (B14:E14). Click OK and Close to get back to Excel. These “down” error bars (whiskers) extend from the bottom (left) edge of the 2Q Box downward (leftward) into the Bottom series.

To add the up whisker, select the 3Q Box series,then in the Chart Tools > Layout tab, click Error Bars, and select More Error Bar Options from the bottom of the menu. Choose the Plus direction, select Custom for Error Amount, and click on Specify Value. Leave the contents of the Negative Error Value box alone (“={1}”) in the mini dialog that appears, then clear the Positive Error Value box and select the Whisker+ row from the table (B15:E15). Click OK and Close to get back to Excel.

These “up” error bars (whiskers) extend upward (rightward) from the top (right) of the 3Q Box.

Now we can format the boxes. Select the Bottom series, and apply no fill and no border, so it is hidden. Then select each of the 2Q Box and 3Q Box series, and apply a dark border and a light fill.

– –

Adding the Mean

To add the mean as a series of markers, select the Mean row in the calculated range (highlighted in blue). If you are making a horizontal box plot, hold Ctrl and also select the Offset row (highlighted in green), so both areas are selected. Copy the selected range.

Select the chart, and use Paste Special to add the data as a new series. If you are making a horizontal box and whisker diagram, check the “Category (X Labels) in First Row” box. The “Series Names in First Column” box should already be checked.

The new series is added as another column or bar stacked on top of the existing ones.

– –

Select this new series, then on the Chart Tools > Design tab, click on Change Chart Type. If you are making a vertical box plot, choose a Line Chart style. If you are making a horizontal box plot, choose an XY Scatter style.

– –

The points in the horizontal box plot are in reverse order. To change the order of points, select the secondary vertical axis (right edge of the chart), press Ctrl-1 (numeral one) to open the Format Axis dialog, then check the “Values in Reverse Order” box.

If you’re making a horizontal box plot in Excel 2003, this last process is a little more involved. Excel draws both secondary axes, but the vertical one is hidden behind the primary axis with the text labels (below left). Double click on the secondary horizontal axis (top of chart), and on the scale tab of the Format Axis dialog, check “Value (Y) Axis Crosses at Maximum Value” (below right).

– –

Horizontal Excel 2003 Box Plot (step 5c)

Excel 2003, continued: Double click the secondary vertical axis (right of chart), and on the scale tab, check “Values in Reverse Order” and uncheck “Value (X) Axis Crosses at Maximum Value” (below left). Finally, select the secondary horizontal axis (top) and click Delete; Excel will now plot the XY series on the primary horizontal axis.

Horizontal Excel 2003 Box Plot (step 5d)

– –

Horizontal Excel 2003 Box Plot (step 5e)

All Versions: Now format the mean series: remove the line, and use an appropriate marker of a contrasting color. If you’ve made a horizontal box plot, hide the secondary Y axis (right edge of the chart) by choosing no tick marks, no tick labels, and no line in the Format Axis dialog.

– –

That was easy and didn’t take too long.

Box and Whisker Charts in Peltier Tech Charts for Excel 3.0

This tutorial shows how to create Box and Whisker Charts (Box Plots), including the specialized data layout needed, and the detailed combination of chart series and chart types required. This manual process takes time, is prone to error, and becomes tedious.

I have created Peltier Tech Charts for Excel 3.0 to create Box Plots (and many other custom charts) automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of chart types.

The utility creates vertical Box Plots …