In a comment to another post, Lynn buttered me up a bit, then asked how to make a certain chart for her boss. Lynn has to plot ten employees’ hourly wages against recommended maximum, minimum, and medium wages. Only four actual wages fall within the recommended limits. I can think of many ways to plot actual values against limits, and I’ll show a few here. If you have any favorite approaches, tell us about them in the comments.
Lynn didn’t provide the actual values, so I constructed this little table.
I’ve repeated the recommended values for each employee beneath the actual wage. I’ve also sorted employees in increasing order of hourly wage, which makes charts easier to understand.
Simple Line Chart
For the chart I used a simple line chart. I formatted the limits to show lines without markers and the actual wages as markers without lines.
This is somewhat reminiscent of a Run Chart.
Colored Bands
That may be simple enough, but an alternative approach uses colored bands rather than lines to denote limits or regions within the data. To start constructing bands, I converted the limits from lines to columns.
At first the columns are clustered and non-overlapping, but I changed them to 100% overlapping. The white Min series blocks the bottom of the green Mid series, which blocks the bottom of the red Max series. I changed the gap width to 0% so the columns were all touching. Finally I removed the borders so the columns merged into bands.
For a simple data set like this, the column chart is perfect. You could also use an area chart approach to construct Horizontal Bands in the Background of an Excel Chart, and you are not limited to parallel horizontal bands: you can Show Uncertainty in Predictions with Shaded Bands.
Number Line
Another way to show data against limits is along a number line. The data is a little different. The Y values of 0.1 for the actual wages and -0.4 for the limits were arbitrarily chosen to align the chart features nicely with a Y axis scaled from -1 to +1.
Here are the data points. The Min, Mid, and Max points have data labels beneath the points and positive Y error bars of length 1.
The Y axis is unnecessary, and a little formatting dresses up the chart.
Peltier Tech Update
New posts on this blog have been few and far between for the last few months. It’s certainly not for lack of material. I keep saying I’ll start writing more, but things keep getting in the way.
I’ve had to spend hours on updates to my charting programs to account for differences in the way Classic Excel (up to 2003) and New Excel (2007 and later) handle different languages and regional settings. I’ve had a few unexpected issues in other projects that have sucked up time. And once momentum is lost, it becomes inertia.
My new hobby, learning guitar, is proceeding steadily but slowly. I am still pretty bad, but the things I’m bad now at much more difficult than what I was bad at last month. The theory (scales, chords, progressions) is coming easily, since it’s largely math, and I seem to have a pretty good ear. What’s more difficult is getting my sausage-like fingers into position on the right fret and the right string in a reasonable time. I’m toughening the skin of my fingertips and learning not to squeeze so hard that I get deep grooves after playing.
I spent a couple weeks on Cape Cod and a couple weekends on the coast of Maine. I’m not a beach kind of guy, but I bought a kayak (my other new hobby) and put on 60 or 70 miles. We stay next to a river on the cape which I never thought much of, but I’ve learned it’s an estuary with many convoluted side branches, supporting much wildlife, and subject to strong currents if you misjudge the tide.
Now the family is getting back to school as teacher or pupils, and the daughter who graduated from college is starting her new job and her career. So I’ll have fewer excuses and more opportunities to keep this blog current.
derek says
I had someone consult me for “a good graph” the other day, and I said “Okay, let’s have a look st the data, and see what we can do.”
“Oh no, I hadn’t thought as far as collecting data yet, I just thought you might tell me what would be the best graph for it!”
Dave says
Some time back, there was mention of your writing a utility to create panel / trellis / latice / multivariate data display type graphs. Do you know when it might be available and if so, how can O trial it? I use SAS for data management and heavy lifting as we process hundreds of millions of log records daily, but I use Excel for summarizing & presenting my data for workload and resouve planning for our mainframe datacenters. I am reasonably adept at Excel but having some more robust chart types available without jumping thru hoops would make my life much easier.
Thanks for a great blog and service.
dave
Jon Peltier says
Dave –
The panel chart utility is still on my short list. This summer I have been unable to make any progress on it. I am hoping to continue work on it soon.
AusSteelMan says
Hi Jon,
I just wanted to write to say a big thanks for helping me out. A couple of months ago, one of the Process Engineers came to ask me if I could help with charting her data. She had datalogger data from one of our Boilers, and knew that the only way she could get it on to a chart would be using dual axes and SCALING some of the data so some of the other series wouldn’t be too flattened – Talk about a Yuk solution!
I did a quick search and found your step-by-step on panel charts. She sent me her data: all 9 series and just under 8000 records, collected every few seconds for just over 2 hours. So I used some and got started. I was impressed, despite there being a few issues.
I kept tinkering and ended up with a 9 series, 7 variable height panel, user selectable start and end dynamic named range chart, even though it still had a couple of issues. I used cell references in the formulae so that things like the number of major divisions and %age gap between the top/bottom of the panel to the series line.
The Series labels couldn’t be added, so I had to keep the legend. Turns out 2003 (at home) would add the series label, but 2000 (at work) would not. More seriously though, the X-Right axes still wouldn’t go to the right. So I ended up with all the axis labels on the left and used the label tool to move them so they did not clash with each other.
I decided I wasn’t finished. The PE told me her data was only a test sample and the real data collection was yet to be carried out. So I turned my attention to making a “template” so that her new data could be effectively be “pasted in” and it would all just work.
In the last 2 days I attempted to go 1 step further and have a template that data could not only be pasted into, but gives the user the choice to change which panel the series will appear on.
I also discovered why I was having problems with the X-Right axes. The X-Axis is made up of HH:MM:SS not days (or categories). So I utilised the DataPointNo (that I had previously added for assisting the user determine which part of the data range the user wanted to see). Works a treat.
Anyway, I realise I have blabbed on a fair bit and I apologise for that. I am just a bit excited right now – although that means getting back to my normal job!
If you are interested, I have posted a truncated file as show below (only has about 1000 data points per series) so it is only a 7MB file. The real one is about 28MB (although at one stage with the uncleansed data it was up near 54MB).
http://www.4shared.com/document/GHCXnmX9/PanelChart-23BoilerTrends.html
Nearly forgot, I too would certainly be interested in seeing the panel chart utility.
Anyway, thanks again,
Darren
ps sorry again about the length of the post.
Jon Peltier says
Hi Darren –
Don’t worry about the post length. I like hearing how people have used the techniques I’ve posted. You’ve synthesized quite a solution using a fair number of different tricks. Better, you’ve turned complicated data into clear information, and made it fairly easy for another person to use.