When the PC started its shutdown trick last month, I did a full backup, and Mozy (my online backup service) seems to have done its stuff through the end of last week, so I probably didn’t lose more than a few days work.

The service guy at Staples (where I bought the sick laptop and most other of my computers) is extracting the contents of the hard disk to a new 1.5 TB external drive. I was planning to put all these files onto my old desktop until I could justify a new laptop, but Staples had an attractive deal on a new laptop, and my will was weak. Wide screen, Dual-core AMD processor, 4GB RAM (well, 2.75 with a 32-bit OS), 320 GB hard drive, so I can store more than about three VMs.

After the files have been recovered, we’ll see if something easy like a new fan will revive the box. If so, I’ll have a spare. If not, I’ll take it and a couple other old machines to be recycled.

My first move, once I got the new laptop home, was to wipe out the Vista installation with all the OEM crap (Norton, Office 2007 Trial Version, etc.), and install the Windows 7 RC. It looks a lot like Vista, and it runs smoothly. Lots of RAM helps here. Then I installed Office 2003 and VirtualPC. I’ll set up a few VMs with XP, Vista, and Windows 7, and Office 2000, 2003, and 2007.

It used to be fun and exciting to get a new computer. Now it’s more of a pain. Install this, uninstall that, try to remember your favorite settings and shortcuts and zillions of passwords. I’ll be back in action in another day or two.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

1. I look at the Sales chart and look for anomalies in sales by guy.  For example, I know Syd has good territory so I’m not surprised that he has a larger slice.  David, however, has a crappy territory but I know he had a big order, so again no surprise.  Bob also has a bad territory and he didn’t have any large sales, so that would induce me to look into the details and see if there is an error.

2. I look at the Sales chart compared to the Commission chart.  They will never be perfectly proportional because guys have different commission rates.  Also, some of the sales are non-commissionable.  However, I want a chart that points out anomalies so I can investigate for errors (mistyped percentages, misapplied commission, etc).

Here is Dick’s data, and the two pie charts he uses.

Well, aside from the tiny labels (which may just be that way in this hastily hacked together example), the charts are pies, which as we know are generally not a particularly effective way to display data. I’ve decided to try this data in a panel chart.

The first thing I did was convert Dick’s data to a List (or a Table in Excel 2007), to allow easy sorting by any of the relevant columns. I added a column for Ratio, which was simply Commission divided by Sales. I formatted the Ratio column as a percentage and the Sales and Commissions as thousands. I added five more columns, one for each of the columns so far, and two spacer columns. The additional Sales column has this formula in cell E2:

=B2/MAX($B$2:$B$7)*0.95

This gives the largest Sales number a value of 0.95, and the rest are proportionally smaller. The additional Commission and Ratio columns have similar formulas. The spacer columns simply have the amount to bring the cumulative total to 1 or 2. For example, the formula in F2 is:

=1-E2

Start building the chart by selecting the data. Select A1:A7, then hold CTRL while selecting A1:I7, so both areas are highlighted. Insert a stacked horizontal bar chart.

Let’s do a little clean-up. Change the horizontal axis scale maximum to 3 and spacing to 1, remove the gray plot area fill, hide the two spacer series (no fill, no border). Format the vertical axis so the categories are plotted in reverse order (this puts the list in the same order as in the wheet), and so the axis crosses at the maximum category.

We can add a dummy series to add labels above each panel of the chart. Put this helper data into a range someplace:

Copy the data, select the chart, and use Paste Special with the following settings to add the data to the chart as a new series in the proper orientation.

The result is another bar chart series stacked on the rest. it’s not visible, since its values are all zero. Select the series by selecting one of the other series, and press the up or down arrow until this series is selected. Change this series to a Column chart type. Excel automatically put this series onto the secondary axis, and uses the labels on the secondary category axiss along the top of the chart.

Hide the column series by formatting it with no borders and no fill. Hide the primary value axis tick labels (lower horizontal axis).

Finally, if desired you can add value labels to the bars. Since the bars actually plot fractions, we will use Rob Bovey’s Chart Labeler, a free add-in which interfaces smoothly with Excel. Select the chart, click on the XY Chart Labels item on the Tools menu, then Add labels, and make the appropriate selections for the Sales series, then repeat for the Commision and Ratio series.

Now it’s an easy task to check the relative sales of each employee. You can sort by Sales, and see whether the Commission data is similarly sorted, or note whether there may be an error in the figures. The Ratio data is a further check that commissions are appropriately calculated.

So how would you analyze this data?

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

Microsoft has violated its own published user interface guidelines that it offers to designers. Ironically I cannot find the same version of these guidelines that I remember: they came out during the Vista beta program, and have since been edited and re-edited, and moved from place to place on the Microsoft web site. However, these guidelines were well stated and even seem to be based on our knowledge of human cognition.

The largest single region of the human brain is the cerebral cortex, which is tasked with parallel processing of large quantities of visual input. In contrast, our short-term memory is capable of holding only about seven pieces of information, and transfer of information into and out of this short-term memory is not always efficient.

Display All Options

The first interface design guideline I will discuss is that whenever possible the interface should show all options which are available to the user. For example, the Excel 2003 Format Axis > Patterns tab shows all axis tick options, allowing us to use our vast image processing capability, rather than load the options into short term memory:

In contrast, the Excel 2007 Format Axis > Axis Options dialog shows only the currently selected axis tick options. Sure, I “know” the other choices, because I have formatted hundreds of thousands of chart axes in my Excel experience. But I still have to stop to think what these options are, or click on each dropdown in turn to remind myself.

The dialog in 2007 is smaller than that in 2003, but it is not as self-explanatory, and isn’t the 2007 dialog larger anyway? (See below for an analysis of dialog sizes.)

Show Closely Related Subtasks Together

The second interface design guideline states that controls for tasks or subtasks that are done together should be placed in proximity to one another. My “good” example here is the Excel 2003 Format Series > Patterns dialog, which contains the options for Area (Fill) and Border for area, bar, and column charts, and the options for Marker and Line for line and XY charts. For an XY chart, for example, the controls for selecting markers, line styles, and colors appear on a single screen:

Excel 2007 provides a “bad” example for this guideline. The same options as above require the Excel 2007 user to jump between SIX different views: Marker Options, Marker Fill, Line Color, Line Style, Marker Line Color, and Marker Line Style. There is a lot of empty space on these six panes. When various preliminary options are made, this space fills with the various formatting options (gradients, fills, etc.) which were added to Excel 2007. These options would be more effectively added as child dialogs, as they are not used as much as “plain” formatting; in fact, many would say that their use runs contrary to good charting practice. Relegating fancy formatting to child dialogs would enable the separate items to be once again combined onto a single dialog view, and suppress the urge to add chart-junk formatting to charts.

Dialog Size

It is worth a brief digression to examine the old and new dialogs. The Excel 2007 chart formatting dialogs appear a lot larger then their 2003 predecessors, but the practical increase in size is mush smaller.

I’ve reduced the images of the dialogs to 66% of their original size, outlined the working region in red, and highlighted the dialog client area in yellow.

- - -

The Excel 2007 dialog is 20% larger than the 2003 dialog, and the client area (yellow) is 22% larger. However, the working area (red outline) is only 6% larger. Most of the increase in area is consumed by the “tabs”, which are compactly arranged in 2003 and displayed in a list in 2007 which fills at most half of the space given to the list. (So there is  plenty of room in the list to restore missing tabs, such as Error Bars and Data Labels.)

If the area of the 2003 dialog were made as large as that of the 2007 dialog by increasing its height, it would have 22% more space to accommodate enhanced formatting, not 6% for formatting and 16% for empty space.

- - -

Maximize Interactivity

The Excel 2003 Format Axis > Scale dialog is an elegant example of interactivity that makes a user’s job easy. Clicking the checkbox or its label changes the value of the cheeckbox. Clicking also changes focus to the box with the value and highlights the entire value, saving the user a click and drag. For example, I can change the minimum from 0 to 1 with two minor actions: click on the Minimum checkbox, then type 1. Also, the value entry boxes are always enabled, so I can click on one anytime. If I change one of the values and the checkbox is checked, it automatically unchecks itself.

One change in the 2007 version of the Axis Scale dialog may even be an improvement. Often inexperienced users would enter a desired axis scale value i the box above and then click Auto, which then told Excel to use its own value and ignore what the user entered. Giving the user a choice between Auto and Fixed may clarify the definition of “Auto” in this context.

The improvements end here, and the functionality is decreased. First, notice that the entry boxes are disabled if the Auto options are selected. This means the user cannot just highlight a value and overtype a new value to change to a fixed value. The user mush select Fixed first. Selecting Fixed, unlike in the 2003 version, does not give focus to the value entry box, nor select the current value. This forces more mouse actions on the user.

This lack of interactivitiy compared to the earlier version of the dialog indicates a few things to me. First, there was a lack of familiarity with the dialog among the design team. Second, there was a lack of resources to implement the dialog completely (which was evident during the technical preview phase). Another sign of a hastily implemented 2007 dialog is that the Fixed option buttons are all one pixel higher than the corresponding Auto option buttons.

Summary

My colleague Dennis has pointed out in a comment to Blue Ribbon Interface, my recent post summarizing user complaints about the Excel 2007 Ribbon interface, that this interface is version 1.0, and that we should expect improvements in version 2.0. I agree completely that the 2007 interface is not mature, though I consider it more of a 0.9 version.

I expect changes in the Ribbon, but I fear that other aspects of the interface design, such as dialogs which reduce productivity as much as or more than the Ribbon, will remain in version 0.9 in the next release of Office. The Ribbon is a new innovation, receives a great deal of attention, and will likely see a great deal of development. On the other hand, dialogs are an old technology, not considered worth deploying scarce resources to improve.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

Author Robert Benchley once said, “The biggest obstacle to professional writing is the necessity for changing a typewriter ribbon.” The modern equivalent may be that the biggest obstacle to professional work in Office 2007 is an arbitrarily changing ribbon.

Simon Murphy has hosted a week’s worth of commentary about the Office UI Ribbon, aka the Office Fluent Interface. This last has been modified to Effluent Interface by its biggest “fans”. Simon uses the phrase frequently, and so do several of his readers, but I think I may have actually coined the term in a comment on Simon’s blog.

Simon has pulled no punches about this glaring new interface, and he attracts substantial commentary supporting and expanding on his views. Simon’s readers do not consist of ordinary crybabies; they include the most experienced and advanced Excel users on the planet, and these experts have opinions which should be heard.  Simon’s topics over the past week include:

• Ribbon lovers week
• Excel 2007 ate my work
• Ribbon Will it – won’t it control thingy
• Ribbon tab based litter
• Ribbon screen stealer
• Ribbon Style Princess
• The ribbon file blunderfest

Jimmy Pena covered the ribbon in his blog recently, in a post bemoaning the fact that Microsoft has had to release add-ins to help users find where their favorite 2003 commands have been hidden in 2007:

• Need Ribbon help? You’re not alone.

I’ve covered the ribbon in a few of my own blog entries:

• A Belated Review of Excel 2007
• Changes to Charting in Excel 2007
• What happened to my favorite Excel 2003 Chart feature?

I don’t totally hate the ribbon, in fact. It’s relatively easy to work with as a designer. The XML is relatively straightforward, and there’s a tremendous resource for dealing with the Office 2007 ribbon interface, RibbonX: Customizing the Office 2007 Ribbon, by Robert Martin, Ken Puls, and Teresa Hennig. It’s the book I use most often lately. It would be easier to work with custom ribbon layouts if you didn’t have to close the workbook containing the XML code to edit it, and if the editing tools were more fluid in their behavior. But you can in fact interface the ribbon with VBA, with a little effort.

As a user, however, I think the ribbon is a mistake. The ribbon has morphed into a variable toolbar with large, sparsely positioned buttons. Too few controls are available at a time, and too many are hidden arbitrarily, by the Microsoft philosophy that “These are the controls we know you need for what we know you want to do now”. There is no built-in way for the user to customize the ribbon and show the controls the user knows he wants for what he is actually doing, though third-party developers have tried to fill this gap. And the ribbon isn’t even the most frustrating part of the new interface. Many new dialogs have become awkward and unwieldy, requiring the work of one Excel 2003 dialog tab to be spread over two or more (in one case SIX!!) dialog tabs.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

Chandoo: June was Pointy Haired Dilbert Blog’s Best month ever
J-Walk’s Spreadsheet Page Blog: June Visitor Stats
Daily Dose of Excel’s June Stats
PTS Blog’s Web Stats – June 2009
Debra’s Contextures 200906 Site Stats

I thought it would be interesting to compare some of my favorite blogs and web sites. Unless you have access to the data for each site, it’s not so easy. One service that lets you make comparisons is Alexa. Using Alexa you can compare up to five sites in a number of categories. I usually look at Reach, Pageviews, and Traffic Rank, which are defined by Alexa as follows:

Reach measures the number of users. Reach is typically expressed as the percentage of all Internet users who visit a given site.

Pageviews measure the number of pages viewed by site visitors. Multiple page views of the same page made by the same user on the same day are counted only once.

Traffic rank is based on three months of aggregated historical traffic data from millions of Alexa Toolbar users and data obtained from other, diverse traffic data sources, and is a combined measure of page views and users (reach).

I plotted a few groups of data, since Alexa only allows five curves on a chart. Since this is my blog, I plotted the Peltier Tech data on all charts, as a benchmark. In the first group I plotted Daily Dose and Spreadsheet Page. Then I wondered whether John Walkenbach’s old site, J-Walk.com, is still getting traffic, taking away from Spreadsheet Page. Finally I added old pal Tushar Mehta’s site, since he’s been around a long time (after j-walk.com but before peltiertech.com).

I’m pulling a slightly larger audience than the other sites, according to the Reach figures. I don’t know what the spike is in early May: it doesn’t show up in any other website analytics service I follow.

Daily Dose and Spreadsheet Page are close to each other, and it looks like j-walk.com still has a significant presence. John could do some .htaccess magic to transfer the link juice to his newer site.

It would be nice to be able to rescale the Y axis, maybe 0 to 0.007%, to provide a little resolution at the low end. Or maybe use a log scale.

Pageviews is a hard chart to analyze. Again, stretching the Y axis to scale from 0 to 0.0002% or using a log scale would spread out the low-end values.

Traffic Rank is shown on a logarithmic scale. I don’t know what kind of algorithm Alexa uses to get Rank from Pageviews and Reach, but it must be somehow multiplicative. A slight advantage in both measures of Peltier Tech over the others means Peltier Tech is visible near the bottom (actually, alternating above and below the X axis), while the others are apparently below the axis.

In my next group, I included The Spreadsheet Page (again), and I added Chandoo’s Pointy Hairde Dilbert, since he started this whole discussion. I’ve also included Debra Dalgleish’s popular Contextures web site, and also Chip Pearson’s encyclopedic site.

Chip pretty much owns the rest of us in terms of Reach, except for my early May spike, and Chandoo’s Lifehacker spike in mid-June. Debra’s site is consistently close to Chip’s and a bit higher than mine. PHD and Spreadsheet Page track each other fairly closely, except for Chandoo’s spike in June.

As before, the inflexible Y axis scale makes Pageviews simply a reminder to mow the lawn.

Traffic Rank shows all of us venturing into visible territory, with the relative placements similar to those shown in the Reach chart.

To put this all into perspective, I decided to compare my site to a few larger ones, namely Amazon, Microsoft, and Google. These sites are a little more popular, so Alexa’s Y axes expand upwards to accommodate them (and push my data ever lower in the charts).

If you look closely at the Reach chart you can see Peltier Tech: it’s the blue line obscuring the X axis. Compared to Google, Microsoft and Amazon are near the bottom. On a log scale, they’d be much closer to Google than to Peltier Tech.

Again, Peltier Tech is coloring the X axis blue in the Pageviews chart. Again, Google’s numbers dwarf Amazon’s and Microsoft’s.

At least the Peltier Tech site shows a little texture in Traffic Rank. Good thing that’s a logarithmic scale! Google’s rank serves as the gridline for Y=1. The log scale also helps MS and AMZ stay near the top of the chart, with ranks around 15 and 30.

So what does all of this mean? I don’t know, but it’s a fun way to kill a little time. I guess I can rest assured that the Peltier Tech web site is in the middle of the pack of the popular Excel sites. But if all of you readers tell your friends, and twitter all about my site, and your friends all post on FaceBook about Peltier Tech, and I don’t know, we throw in a little MySpace and Digg and Technorati and other funny sounding words, and the momentum grows, I’ll probably still be firmly lodged in the middle of the pack.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

Pointy Haired Dilbert statistics for June 2009

John Walkenbach responded with his own visitor stats.

The Spreadsheet Page statistics for June 2009

John asked, so here are my June stats, first for the entire Peltier Tech website (which includes the blog), then for the PTS Blog.

PeltierTech website statistics for June 2009

PTS Blog statistics for June 2009

This was almost the best month for the web site, but March (below) was slightly better (February and April were in between March and June).

PeltierTech website statistics for March 2009

June was the highest month for visits and visitors on the blog, but April had more pageviews.

PTS Blog statistics for April 2009

My stats show “typical” daily variation, with high weekday and low weekend, for visits, visitors, and pages, while the other measures are flat. John has the same daily variation in visits, visitors, and pages, while hispages per visit and time per visit are higher on weekends. I think this means that people spend more time on his site on weekends, when they have more time to poke around.

Chandoo’s stats in the second week of June are crazy, higher and less regular than the rest of the month, which may show a regular variation except for the distraction of the second week. The peak in Chandoo’s numbers probably corresponds to the June 12 announcement of the new Chart Busters feature he and I are working together. This announcement led to some controversy, as it at first used the name Chart Doctor, which is also the name of a  feature on Kelly O’Day’s Process Trends web site.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

After my earlier article about chart colors, I used a ColorBrewer-derived palette in Excel 2003. At this point, though, I’ve decided I’m not terribly fond of this palette: the colors are fine together, but they are not the “pure” colors I’d like. The red has an orange tinge, the green is shaded somewhat towards blue, the blue is a bit purplish, etc.

So I’ve changed palettes to be more in line with the colors that one would expect to find, as set forth by Stephen Few.

Here are the default Excel palette, my previous palette, and my current palette. Notable changes are the evolution from Excel’s unattractive default charting colors in the bottom two rows of the palette, to the ColorBrewer derived colors, to the Few inspired colors. In the earlier custom palette (center) I also lightened up some grays in the right column of the palette, and also changed the green color (fourth tile in the third row) to something more visible. I replaced some default colors from the fifth row of the palette with the lighter versions of the Few inspired colors, so I moved these replaced colors to the top row, where the default colors are all too dark to distinguish (though I may decide to bring back the brown color).

- - - - - -

Here is the ColorBrewer2.0 screen for the colors in my earlier palette. Compare the bottom row of the center palette above to the column of colors in the bottom left of the ColorBrewer screen.

And here is an excerpt from Stephen Few’s white paper Practical Rules for Using Color in Charts where he talks about colors. I’ve used Few’s darkest set of colors (middle row) for my dark chart colors, I’ve lightened his medium-dark colors (top row) somewhat for my chart fill colors, and I’ve added highlighting colors which are slightly darker than his light colors (bottom row).

Why am I still worrying about the Excel 2003 color palette? I suppose I should start using Excel 2007 more, especially since the release of the Excel 2010 beta is imminent. But I have not yet become comfortable in Excel 2007, especially for charting, so I would just as soon use 2003. I will keep using 2003 for my chart images, and if a protocol in 2007 differs by enough to confuse readers, I will then capture whatever screen shots are needed.

If you are thinking about colors for a blog or web page theme rather than for Excel, check out the Color Sceme Designer. It is pretty flexible. Its emphasis is on graphic design colors for web page schemes, not for charting (though the two purposes are related).

Update 30 June 2009

I’ve updated the Palette Chooser utility so it includes the original and adjusted Few-inspired palettes. The Palette Chooser was created in Excel 2003, and should also work in Excel 2002 and 2000. I don’t know whether it will work in Excel 97 or in any flavor of MacExcel, and it is irrelevant to Excel 2007.

Download and unzip the Palette Chooser zipped workbook. When the file is opened, the Format menu has an added item called Custom Palette; this menu item is removed when the file is closed.

Colors are stored in the Colors worksheet of PaletteChooser.xls. Palettes are laid out in four-column ranges, with the color index, red, green, and blue values for each color to be modified. Follow the same format to define your own color palette, then assign a worksheet-level name to the range containing your definitions. This name is what is listed in the Palette Chooser dialog.

To modify the palette of a workbook, activate it first before selecting the Custom Palette menu item. The Custom Palette command launches the Palette Chooser dialog. The listbox includes a Reset item, which reverts to the palette which was active in the workbook when the dialog launched. The other listbox items are palettes defined by the named ranges containing the color information.

Here are the Set2_Dark2 (my previous palette) and Paired palettes, based on ColorBrewer:

- - -

Here are the Pastel1_Set1 and Pastel2_Set2 palettes, also based on ColorBrewer:

- - -

Here are the original and adjusted Stephen Few-inspired palettes:

- - -

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

I had a web meeting with a couple of my clients this week. Normally I use GoToMeeting for this purpose, but the client set it up, and we used WebEx. When I selected a service a few years back, I rejected WebEx in favor of GoToMeeting, because I thought GoToMeeting had the nicest, smoothest, most intuitive interface of all similar products. The WebEx experience this week reminded me why I chose GoToMeeting, and showed that WebEx has not progressed in several years.

I had to download the WebEx software. The download was fine, but installation was a little shaky, and when installation was finished WebEx started, but unlike GoToMeeting, it didn’t remember which meeting link I’d clicked on to initiate the download and installation. Fine, I reopened the email and clicked again on the link. At one point I had to take control of another participant’s desktop, which is intuitive in GoToMeeting: click on a clearly-labeled button. In WebEx I had to right click on an unlabeled icon (without even a tooltip) to request control before the other user could grant permission, and he had to grope around to find the control to accomplish that. The WebEx connection was not as smooth or as quick as GoToMeeting, and the image quality was so poor I could barely read any text. There also didn’t seem to be a VoIP option. The final insult was that when the meeting ended, FireFox crashed.

I asked my client why they use WebEx; the answer was that the boss uses a Mac so they couldn’t use GoToMeeting. Well, a quick trip to their web site showed that this was untrue. So I emailed the client to say that (a) GoToMeeting works for Macs, and (b) the boss should get a real man’s computer.

I have a standard subscription to GoToMeeting, which runs $49/month, and allows unlimited meetings with up to 15 participants. I’ve used it to diagnose problems on client machines, because seeing what’s happening is much more informative than “I get an error”. I use it to demonstrate add-ins and techniques, because I can show something on my desktop, then watch and direct on the users’ desktops, and they learn better than following even a detailed web page. I used it to help my daughter, who’s away at college, clean some malware off of her computer. The subscription paid for itself when one user, who fancied himself as something of a programmer, asked me to walk him through the code so he could document it (he added comments like “Declare Variables”, “Start Subroutine”, and “Set bTest Equal to True”): though remarkably tedious, it was uch better on screen than over the phone..

I’m not getting any benefit from saying this, but I’ll say it anyway: GoToMeeting is by far the best web meeting service I’ve ever used.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

The chart in question is a big round circular mess, posted on Flickr by Petra Axlund of 5W Infographics. Below is a smaller version of the chart, but you can see the original in all of its glory by clicking on this one.

Click on the image for a larger view.

The chart is eye-catching, but not very informative.

So what’s wrong with the chart?

There’s a problem with arc length vs. value. The longer arcs, that is, those representing the longest times, are also on the innermost rings. This distorts the story. It also looks like the 304 arc, minutes for a woman to bodypump off a pizza, looks like it covers a larger area than the 352 minutes is takes a man to walk off the same pizza. The 105 minutes for a woman to burn off a Big Mac and fries looks as long, or nearly as long, as the 125 minutes for a man to walk it off. Since at first glance it’s hard to tell if this is an error, or due to the arcs and different radii.

For a while I didn’t know whether the calorie counts for the food all started at the inclined edge next to the cut-out. But after careful examination, I think the calories of each section are added to the previous sections. This removes any possibility of gauging values from a common baseline.

Some of the color schemes are hard to interpret. Black numbers on the dark brown beer arrows, and even the balck on dark purple Big Mac arrows, are difficult to read. And these numbers are important to read, because the lengths of the arrows are meaningless. This means the chart without the numbers is not self-sufficient.

The hard to read numbers and the out of scale arrows reduce the credibility of the chart. One other thing bothers me as well. Are those 2647 calories for the whole pizza? When’s the last time you saw a non-teenager eat a whole pizza? Or a teenage girsl?

Finally, it is impossible to do any adjustment of items on this chart. How would you account for a larger individual (who burns more calories per minute), or for onion rings instead of fries, or for biking instead of walking? Impossible.

By the way, what’s bodypumping? Some new age kind of weight training? Or do I really want to know?

How can we improve on the chart?

You don’t need all those numbers. An XY chart with clear axis scales is a large improvement. Instead of drawing completely separate graphical elements for each food item, why not draw some simple lines showing calories expended vs. time for the different exercises? Then draw a line corresponding to the caloric value of the food item, and you can pick off the times required to burn off its calories.

Here is a version with calories plotted horizontally and exercise duration vertically.

This one has its axes swapped, and I think I prefer this orientation.

This simple charting technique has the benefit that different factors can easily be shown. The lines of expended energy vs. time can be adjusted for individuals with different weights (for running and walking, energy spent is almost proportional to weight). Or a different exercise can be placed on the chart: biking would be somewhere between runniing and walking. Or the time to burn off a bacon double cheeseburger and onion rings from Burger King could be placed on the chart at 1220 calories.

If you want you could add some color to the chart, and use bolder lines and text to denote the larger male’s exercise values. I’m not wild about the vertically oriented labels, but I find the food labels more important to show horizontally.

Well, that’s a pretty large chart. If we assume someone will eat half a pizza, we can reduce the scales on the axes, and make the entire chart a bit smaller.

As Jeff points out in his comment, this kind of chart is fine for anyone who is scientifically trained and works with numbers. For those who are not so numerically literate, it might be simpler to show tow separate charts, one with the calorie content of a few representative foods, the other with caloric expenditures for a few popular exercises.

Additional exercise calorie expenditures were based on data in Exercise Calorie Expenditures - Sorted by Intensity.

Maybe we could combine the two charts above, so they use the same scale, and a simpleton user could at least judge that a chocolate chip cookie is at least half an hour of running.

How would you approach this data?

I’ve used this utility in-house for a while, adding little enhancements here and there. I’ve come up with three main ways to use it, illustrated in the dialog screen shots below. Based on the option selected in the top of the dialog, the mode of operation is changed.

Here the data is arranged simply, with the input X and Y in two adjacent columns, and the output Y, calculated using the input X values, placed into the third adjacent column.

Here the input X and Y are in two columns, and the output Y is calculated using the input X values, but is located in another column. This is handy if you are calculating smoothed values for different values of alpha or N and placing the calculations in different columns.

Finally, the input X and Y values are in two adjacent columns, and the output X and Y are in two other adjacent columns, not adjacent to the input columns. Here the output X values need not be the same as the input X values.

When installed, the utility places a control on the PTS Charts menu. If you haven’t installed other PTS Chart Utilities, this menu will be created first.

Compare my weight over the past three years, smoothed using a seven-day moving average…

… and using the LOESS utility. The main trends are plainly visible, while the short term fluctuations have been removed.

The utility is a regular old Excel add-in, which can be downloaded in the zip file LOESS.zip. Install this add-in using the protocol in Installing an Excel Add-In or Installing an Add-In in Excel 2007.

Try it out, and tell me what you hate about it.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.