|
|
|
Peltier Tech Excel Box and Whisker Plot (Box Plot) Utility Documentation
Box and Whisker Charts (Box Plots)
Box and Whisker Charts (Box Plots) are commonly used in the display of statistical analyses. Unfortunately, Microsoft Excel does not have a built in Box and Whisker chart type. You can create your own custom Box and Whisker charts, using stacked bar or column charts and error bars, in combination with line or XY scatter chart series to show additional data. Elsewhere on this website there are tutorials showing how to construct box and whisker charts in Excel. This is a tedious manual process, however, and this page describes a utility I've written that enables you to easily make such charts automatically. You can use the straightforward dialogs to build box and whisker plots, or you can call this utility from your own VBA procedures. About the Peltier Tech Box and Whisker Chart UtilityThe Peltier Technical Services Box and Whisker Chart utility allows the user to construct box and whisker charts directly from raw data; the utility builds an intermediate summary table and then creates and formats the chart from this table. The utility is designed to work in Windows Excel versions 2000 through 2007. Installing the Peltier Tech Box and Whisker Chart UtilityIf you have installed an earlier (demo) version of this utility, you should uninstall that version before continuing. Choose Add-ins from Excel's Tools menu, locate Box Charting Utility in the list of add-ins, uncheck the box in front of the entry, and press OK.
To install the Box Chart utility, close Excel, then double click on the PTS_Box_Charter_Setup.exe. The Box Chart utility is installed as an add-in, in a new "PTS Charts" subdirectory of your computer's Program Files directory. In addition, "DataBoxPlot.xls", a workbook containing sample data to show how to use the utility, is saved in "PTS Charts", a new subdirectory in your My Documents directory. Using the Peltier Tech Box and Whisker Chart UtilityWhen you install the add-in in Excel 2000 through 2003, it adds a menu called PTS Charts to Excel's Worksheet Menu. In Excel 2007, the utility adds a custom PTS Charts ribbon. The utility then adds a "Box and Whisker Chart" button to the menu or ribbon. Select a range of data to chart, click this Box and Whisker Chart button, and the dialog appears.
About the Data The Box and Whisker Utility is designed to calculate statistics on a range of raw data. A frequent question I've received from users is "How can I use the utility to chart data which has already been compiled?" I always have said that this feature is not supported, but maybe it will be incorporated into a future version of the utility. I recently had an "aha!" moment. Use a five-value range as input into the utility, where those five values represent calculated minimum, 25th percentile, median, 75th percentile, and maximum. For all quartile calculations except for Minitab's, the utility's calculated quartile breaks based on the compiled input values match the compiled input values. Therefore the utility has had the requested capability all along. Naturally this applies only to the box and whisker quartiles and four box quartiles options, and not to the box and whisker with outliers style. You should ignore the calculated averages, since the average of these five parameters is meaningless. If you have average values, you can paste them in the output table where the calculated values are stored.
Output of the Utility The output produced by this utility consists of a new worksheet with a data table and an embedded chart. The chart is a standard Excel chart, somewhat larger than the Chart Wizard's default size; it can be moved, resized, and otherwise formatted as any other Excel chart. The chart is a combination chart with a number of stacked column or bar series and line or XY series.
Notice that the stacked columns in vertically aligned box plots are aligned directly above the columns of data in the worksheet. This alignment is maintained for any number of categories across the chart and table, as shown below. The sizes of horizontal box and whisker charts are also adjusted to keep the charts on screen while maintaining a reasonable aspect ratio.
Peltier Tech Box and Whisker Chart OptionsThe Box and Whisker Utility produces three styles of box plots: Simple Box and Whisker Chart, Four Box Quartile Chart, and Box and Whisker Chart with Outliers.
Box and whisker quartile chart The inner quartiles of each sample are represented by light gray boxes, separated at the median by a thin line. The lower and upper quartiles are represented by whiskers. The average of each sample is represented by a diamond marker overlying the box and whisker for that sample. Four-box quartile chart The inner quartiles of each sample are represented by medium gray boxes, separated at the median by a thin line. The lower and upper quartiles are represented by light gray boxes. The average of each sample is represented by a diamond marker overlying the box and whisker for that sample. Box and whisker chart with outliers The inner quartiles of each sample are represented by light gray boxes, separated at the median by a thin line. The height of the gray boxes together makes up the interquartile (IQ) range. The range of data falling within 1.5 interquartile ranges of the median is represented by whiskers. Any outliers that fall between 1.5 and 3 IQ ranges from the mean are represented by asterisk markers, while any outliers falling beyond 3 IQ ranges from the median are represented by circular markers. The average of each sample is represented by a diamond marker overlying the box and whisker for that sample. Each of these box and whisker chart styles is available in vertical (column) and horizontal (bar) variations.
The Box and Whisker Utility computes quartiles in four different ways, which can be changed on the fly by the user: Tukey hinges, CDF quartiles, Minitab quartiles, and Excel quartiles. The Tukey and CDF techniques are generally close to each other (identical in the data set below and therefore shown in just the first chart), Minitab calculates the widest interquartile ranges (fewer outliers), and Excel calculates the narrowest interquartile ranges (more outliers). For my definitions of quartiles, I have relied on Quartiles in Elementary Statistics by Eric Langford, California State University, Chico, published in Journal of Statistics Education Volume 14, Number 3 (2006).
The user can toggle the display of averages by checking the checkbox.
The user can toggle the display of outliers in a Box and Whisker Chart with Outliers by checking the checkbox.
All of these box and whisker charts are "normal" Excel charts, made from a combination of common chart types, so they can be resized, reformatted, and copy-pasted into other files and applications. The user should make a copy of a chart, and make changes to the copy, to prevent the loss of work if the only chart is edited beyond recognition. Using the Peltier Tech Box and Whisker Chart Utility ProgrammaticallyThe core of the Peltier Tech Box and Whisker Utility is a procedure called PTS_BoxWhiskerChart, which can be called from other VBA procedures. The syntax of the PTS_BoxWhiskerChart function is: Function PTS_BoxWhiskerChart(rngData As Range, _
bByColumn As Boolean, _
bFirstLabels As Boolean, _
bVertical As Boolean, _
lBoxWhiskerChartStyle As BoxWhiskerChartStyle) _
As Chart
rngData is the range containing the raw daya, bByColumn is true if the data is arranged by columns, bFirstLabels is true if the first row or column of the data contains labels for each set of data, bVertical is true to create a vertical (columnar) chart, and lBoxWhiskerChartStyle represents the style of chart, where 0 is a box and whisker quartile chart, 1 is a four box quartile chart, and 2 is a box and whisker chart with outliers. The newly created box and whisker chart is returned by the function to the calling procedure for further execution. The simplest way to use the Peltier Tech Box and Whisker Chart Utility from your own code is to call it using Application.Run, as in the following sample macro. The PTS_BoxWhiskerChart procedure is a function, which returns the box and whisker chart to the calling procedure.
The PTS_BoxWhiskerChart procedure can also be called as a sub, without returning any results to the calling procedure.
Intellisense shows the syntax of the PTS_BoxWhiskerChart function as soon as you type it into a code module:
Intellisense displays the allowable values for the BoxWhiskerChartStyle argument, and allows you to select the desired option from the list:
As with the Application.Run approach, PTS_BoxWhiskerChart can be called as a function, returning the box and whisker chart to the calling procedure.
PTS_BoxWhiskerChart can also be called as a sub, without returning any results to the calling procedure.
Contact MeIn the event of problems with this utility, use the following link to send me an email. While I cannot answer all email messages, I will certainly address any issues that arise. If you are reporting a problem, please describe the problem as clearly as possible, and if relevant include a copy of the data and chart. I will also consider reasonable enhancements to the utility. I do not plan to make the utility compatible with Excel 97 or earlier or with Mac versions of Excel. The extensive data validation required to enable multiple area ranges prevents me from addressing this capability at this time. Contact Jon about the Peltier Tech Box and Whisker Chart utility. |
Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech BlogPeltier Technical Services, Inc., Copyright © 2012. All rights reserved. |
