Nontrivial Exercises

Drawing a Distribution Curve in Excel

I have long asserted that Excel is one of the most powerful tools ever created. It’s also one of the most underutilized - many don’t even realize all the things it can do - as well as one of the most mis-utilized, at least compared to what was intended to be used for.

People use Excel for project plans, taking notes, making lists, as an address book, and a new use that I just saw today - keeping a list of motivational quotes (you know who you are, Steve!). Part of the problem is that there is not an app like OmniOutliner on the PC - one of the cool surprises that I had when getting my Mac.

One of the things you would expect to be able to do with Excel is some statistical analysis. Excel does come with some stats functions that are pretty useful and go way beyond my knowledge of math, but there’s one basic thing it doesn’t do - at least not without some coaxing: plot a frequency distribution curve of the values of a set of data.

For example, I recently wanted to analyze a set of data that represented the times, in minutes, that a certain process took to complete. I had about 40 measurements of this process, and I wanted to see how they were grouped together. I wanted to draw a graph of the instances of the various times to get a visual representation of how the times were distributed across the different values. I wanted to have something that looked like this:

After playing around in Excel a bit I couldn’t find a way to make one of these graphs. After a bit of searching, I found a host of Windows apps that can draw nice frequency distribution graphs - some of which appear useful, most of which are expensive. I worked with Excel for a while and came up with a way to effectively draw a frequency distribution graph.

You do this by making use of the frequency function in Excel and then drawing a graphs with two series of the same data.

First, you decide which frequency groups you want to plot. My data fell in the range of 1 to over 1500 minutes, with a lot of the data towards lower numbers. I decided to group my data in blocks of 25 minutes. This means that I want to see how many of the processes I measured took between 0 and 25 minutes, how many took between 25 and 50 minutes, etc. I set a maximum of 450 for my analysis, so the final group was how many of the processes took 450 minutes or more.

I then setup by data groupings as numbers in successive cells of a column in a spreadsheet:

Next, I used the frequency function to have Excel count the number of occurrences of my data values that fall within each group. When you use the function across a range of cells like this, you have to enter it as an array function. Array functions in Excel are very powerful, but somewhat difficult to understand at first. Essentially they let you do operations on a group of cells at one time, rather than just one cell at a time.

The formula I will use is the following, where the DataSeries!B2:B42 is the group of cells that contain my actual data, and the A7:A25 is the range of cells that contains the frequency groupings I just talked about above:

=FREQUENCY(DataSeries!B2:B42,A7:A25)

To enter this, you first highlight all the cells in the output range - B6 to B25 in my case above. Note that I highlight one cell past the last number in my grouping range so that Excel will put the number of instances greater than the last number in the grouping range.

After you highlight all the cells in the output range, you then enter the frequency formula above in the data entry form at the top of the Excel window:

Now, rather than pressing <ENTER> after typing in the formula, press <CTRL><SHIFT><ENTER>. This enters the formula as an array formula and tells Excel to apply the formula to all the highlighted cells. After doing this, I had the counts of data that I wanted:

You can now quite easily create a graph with this data that almost is what we’re after. I chose to use an area graph like this:

To make this look a little bit nicer and less choppy, I added another series to this graph using a line graph and smoothing the line. First, I simply created a copy of the data values in column B, e.g. cell C7 would be

=B7

Now you have a copy of the frequency data in the worksheet:

In the graph, add a data series (right-click on the graph, choose Source Data, then click on the Series tab, and click add) consisting of the values in the copied column (column C in my example). Then make this data series a line graph (right-click on the new data series on the graph, then select graph type, and choose the line graph.

The line graph now forms a line along the top of the area graph. But, we want to make it smoothed. So, double-click on the line graph and select the Smoothed line option like this:

Now, we have a nicer looking frequency distribution graph:

For a nice touch, you can paste the graph as a picture in somewhere like PowerPoint and then draw a vertical line to represent the median.

There you go - hopefully you’ll be able to use this to visually represent your data better in Excel.