Box and Whisker charts are useful to statistical analysis, when comparing the distributions of data within different populations. A typical box and whisker chart is shown here:
This chart compares the metrics of three populations, indicated here as A, B, and C. The four quartiles are represented for each population: the second quartile (25th to 50th percentiles) are represented by the lower rectangle and the third quartile (50th to 75th percentiles) by the upper rectangle. The first quartile (0th to 25th percentiles) are represented by the error bar below the two rectangles, and the fourth quartile (75th to 100th percentiles) by the error bar above the rectangles. The red symbols across the chart indicate the population averages, and obviously the median (50th percentile) is where the two rectangles above each category meet.
Despite the usefulness of box and whisker charts, Excel has no native capability to produce them. However, the makers of Excel built a lot of flexibility into Excel, if you are imaginitive enough to make use of it. On my web site I show techniques for producing box and whisker charts, both vertical (as above) and horizontal, by creating stacked column or bar charts, hiding some series, and combining with XY series for any added series, such as the average line above.
The procedure is a little complicated, and may get tedious after a while, so some time ago I built myself a rudimentary utility to create box and whisker charts, and I posted it on my web site for anyone lucky enough to stumble upon it. But this week I have made some improvements, and I’d like people to try them out.
The Box and Whisker Charts utility is designed to work in Excel versions 2000, 2002, and 2003; it has not been tested in any Macintosh version or in Excel 97 or 2007, it may or may not work on these versions. The Box and Whisker Charts utility is a beta version, available at no cost in exchange for useful feedback. Ultimately it will be incorporated into a commercial Advanced Charting utility. The Box and Whisker Charts dialog is shown below:
The output of the utility is a table and an embedded chart on a new worksheet.
The Box and Whisker Charts utility is available at Box and Whisker Plots, which also has instructions for its installation and use.
I welcome any comments and suggestions, and will probably implement most reasonable feature requests. Bear in mind that this is another nights-and-weekends kind of project.