Quote of the Day
The greatest mistake we make is living in constant fear that we will make one.
— John Maxwell
Introduction
Figure 1: Standard Icon List Choices.
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.
Background
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. |
Application
Problem Description
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.
Figure 3: Example of My Desired Output.
Issues
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.
Figure 4: Cell Reference with No Relative Addressing.
- 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 =).
Figure 6: Icon Set Dialog Box Configuration.
Conclusion
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.
Tricky and thanks.
I talked to every Excel expert I know, and no one knew a simpler way to use these icon sets for this problem – most did not think it could be done.
mark
Mark,
I did some experimenting and found out the following:
1. You can use "=offset($C$9,0,-1)" in your offset formula
2. I wrote a VBA program to do the same. For this program, I manually set a range going from $L$9 to $L$13 for testing purposes. I also set up a series of numbers in column "K" range to help with that test. I know that you might want to keep down the programmable workbooks. The easy way around that is to put the key macros in your Personal.xlsb workbook. These macros or procedures can then be called from xlsx workbooks to set up condition tests in this case.
Here's the code:
Sub SetConditionTest()
Dim rng As Range
For Each rng In Worksheets("Icon Set").Range("L9:L13")
rng.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
End With
Selection.FormatConditions(1).IconCriteria(1).Icon = xlIconRedCross
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValueFormula
.Value = "=OFFSET(" & rng.Address & ",0,-1)"
.Operator = xlGreaterEqual '7
.Icon = xlIconGreenCheck
End With
With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValueFormula
.Value = "=OFFSET(" & rng.Address & ",0,-1)"
.Operator = xlGreater '5
.Icon = xlIconRedCross
End With
Next
End Sub
Works slick! It is now in my personal macro book. Thanks!
mark
You're welcome
Agreed, the UI for Conditional Formatting in general and Icon Sets in particular sucks.
Note that CFs are what's called 'Super Volitile': they are evaluated each time the cell that contains them is repainted on the screen (which happens say if you use the scroll bar to move the ‘view’ up/down or left/right), even in Manual calculation mode. Meaning it is irrelevent whether you use INDEX for them or OFFSET.
But because no other formulas are ‘downstream’ from conditional formats, then only the conditional format formulas themselves get recalculated. So if you’ve got simple conditional formatting rules, you won’t notice any delay.
Excellent answer! I have always wondered whether volatility mattered for the formulas in a conditional format. Thanks a bunch!
mark