Quote of the Day
The greatest mistake we make is living in constant fear that we will make one.
— John Maxwell
I use Excel everyday, but that does not mean that I have used every feature. Yesterday, I was asked to prepare an analysis for our marketing group of the maximum possible distances over which a customer can be served using various types of fiber optic communication systems – we call this parameter “reach”. During this analysis, I saw what I thought was an ideal opportunity to use Excel’s icon sets for the first time (Figure 1).
I was simply going to generate a check mark if two figures agreed and an X if two figures disagreed – there are many ways to accomplish this task, but I decided to try something new today. What appeared to be a simple application ended up being more difficult than I thought it should be. In this post, I will show how I resolved the issue.
A sample workbook is included here.
When I do analysis work using a spreadsheet, I like to include some side calculations that I call “check figures”, which are non-essential calculations that provide indications that my overall calculations are at least consistent with each other. I include check figures in all my critical spreadsheet work because it is so difficult to catch spreadsheet errors. To remind me of how difficult it is to catch spreadsheet errors, I keep a list of disasters caused by spreadsheet errors (e.g. a spreadsheet error was at the heart of the London Whale incident). The problem of spreadsheet errors has even been discussed in the New York Times. Of course, simple math errors plague even peer-reviewed research papers.
To improve the quality of my spreadsheet work, I keep a close watch on the work of the Spreadsheet Lab at Delft University. These folks are at the cutting edge of helping spreadsheet users improve their quality. For a good presentation on their work, see the video in Figure 2.
|Figure 2: Good Video Briefing on Spreadsheet Work.|
I want to indicate that two cell are either equal or different using a check mark or “X”, respectively, which I illustrate in Figure 3.
There were two issues that I encountered while trying to perform this simple task:
- I could not use relative addressing.
I was floored to find out that I could not simple point at the reference cell to which I wanted to compare my cell contents. I was able to solve this problem by using the offset function to create a reference without any relative addressing (Figure 4). I should mention that I try not to use the offset function as a matter of principal because it is volatile. I will experiment with using non-volatile functions (e.g. Index) later.
- I could not simply copy the format from the first item to all others.
I had to copy the format from the first cell (C9) to all others one at a time. What a pain!
Icon Set Dialog Configuration
Figure 5 shows my Icon Set dialog box configuration. My logic is simple – I put in an “X” in the cell if the two values are different (> or <), and a check mark in the cell if they are both ≤ and ≥ (i.e. equivalent of =).
I was able to make the icon set feature work for my simple application, but I could not imagine a more painful way for Microsoft to have designed this feature.