Off By One Errors.

Today I was trying to do some fancy spreadsheet work using Excel. Now I’m usually pretty decent at this and I’ll be able to figure out if something can be done within a couple minutes or not. If I can do it great, off I go, if not I’ll usually have to code something myself. What really irks me is when something is supposed to work that doesn’t as was the case today. I was trying to do a simple VLOOKUP and for the life of me I couldn’t figure out why the data wasn’t coming out as I expected. That was until I looked carefully at the first couple lines….

There was a leading space on each line, which in Excel’s eyes make it completely different.

The engineers in the room will recognise this as a classic example (well extrapolation) of an off-by-one error. In essence everything about my system was perfect but a simple mistake of not sanitising the data completely ruined all my hard work, and left me chasing my tail for about an hour. This wasn’t the first time I’ve encountered these problems either, and even the most adept programmer/engineer can fall to these problems. In fact I once even stumped my university professors with one.

Rewind back about 7 years to when I was a fresh faced teenager blundering his way through the first year of university. Our first ever programming assignment was to develop, test and implement a simple cipher program. From memory I think it was a Caesar Cipher which is a trivial yet excellent way to show people the ropes of programming. All of the testing was done automatically so you had to be spot on, there was no wiggle room here.

So I coded and I coded and eventually I came up with what appeared to be a working cipher. I hand encoded some text to make sure I had some data I could compare to, and the initial results were good. Everything seemed to be working ok until I tried some longer and more unusual words. The really strange thing was that part of the text would cipher properly and other parts wouldn’t. After showing my code to the university tutor and lecturer I was met with complete disbelief, and they told me to try recoding it from scratch. Since I had a bit of time between classes (4 hour breaks in the middle of the day, yay! :() I attempted it once more, only to hit the same road blocks.

My code was absolutely bullet proof by the end. All bounds were checked and I thought everything was in order, but still I couldn’t shake the nagging half working cipher. It all became clear on the next assignment in which we had to build on our previous work. After not looking at the code for 2 weeks my eyes were fresh and I happened upon one line of code that looked something like this:


Go on, figure out why that one line caused the cipher to break. It’s staring right at you! šŸ™‚

Alright give up? The W is missing. This is why it would work on some words (anything without a WĀ  or any letter after it in the alphabet in it was fine) and not on others. The reason why no one picked it up is due to the way the UV looks very similar to W, well at least on the consoles we were programming on. Adding that one letter in fixed everything, and my next assignment worked flawlessly.

It’s a testament to the fine line we walk sometimes in the engineering field. One small mistake in the right spot can cause the whole system to fall in on itself, and that’s why testing and retesting something is so important. That day taught me that sometimes it’s best to leave a problem for a day in order to look upon it again with fresh eyes, something that I hadn’t really thought of before. It also reminds me of an old quote from the father of the modern computer, Charles Babbage:

On two occasions I have been asked,ā€””Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?” ā€¦ I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

Computers are always right, even when they’re wrong šŸ™‚


Leave a Comment
  1. Wow, I even proof read that string to make sure I had the error right and I put in another error! Nice catch, I’m going to leave that one up there for prosperity šŸ˜€

  2. davidklemke :
    Wow, I even proof read that string to make sure I had the error right and I put in another error! Nice catch, Iā€™m going to leave that one up there for prosperity

    I think you mean posterity šŸ˜‰

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.