When “Equals” Doesn’t Really “Equal” in a Spreadsheet
Here’s how I solved a problem in a complex spreadsheet that was both very difficult yet simple to find.
One day this past week, I fielded an issue that was very difficult to find, but enormously simple.
We call it a “bug” in the sheet’s code, and interestingly it was just as small as the issue that was first found and called a bug. An actual moth was found between two relay connections on this super advanced calculator in 1945, and thus the term “debugging” came into being.
There has been a lot of ink spilled just talking about the term “bug” that we use now, and whether it is correctly used here I won’t argue. But, many software “defects” come into play these days simply because the developer did not follow the “rules”. Not because some outside influence like a real moth came into play.
For my issue, we just needed to take another look at the rules.
In this case, it was a complex spreadsheet (with Java code behind it), but the issue was simple and small, hard to see, took time to find, but was a reminder to follow the rules. And so, when does equal not really mean exactly equal in a spreadsheet?
We had some logic and functions that looked like this below. See if you can see what the issue might be (the text of the functions are in column B and the actual functions are executed in column C):
As you can see above, all we are trying to do is compare the previous group cell with the current group cell. If it is equal, then we return TRUE, if it is not equal we return FALSE. And as you can see above all the Group tests return TRUE. They are all equal.
But if you look really close, you will see the problem. Cell A9, looking closely at it is in fact slightly different – just a few pixels to your eye, but not EXACTly the same. Cell A9 has a capital I in the “TIller Money” group name.
Now we get back to the Spreadsheet rules. For cells that are text values, when you use the equals sign (=) in an IF statement, the equals sign is CASE INSENSITIVE. What happens in the background, is the text cells are shifted to lowercase before the test. So in our example above, it is correct per the spreadsheet rules. The text fields are equal since we are dealing with a case-insensitive match with an equals sign.
So, if you are working with text fields and want an EXACT test that IS case sensitive, you will need to use the function EXACT in your IF statement. The example below is the same data as above in Column A, but we are using the EXACT function in this case:
You can see above that the EXACT function sees the difference in cell A9, and correctly gives a FALSE answer in C9 and C10.
So, remember to follow the Spreadsheet rules and you won’t run into this issue in your custom work that you are doing with your Tiller sheet. This one issue can be hard to find, but has a simple solution.
When working with text, EXACTly equals (including case) requires the EXACT function.