Excel Hell

A friend of mine wanted some help with a spreadsheet which, most unfortunately, was written in Excel. The help he needed was in creating a formula that would allow him to rank winners of a golf tournament that involved teams of three to five players. Depending on the number of players in each team, he wanted to rank only the best balls from each team.

As an example, if there were five players on each team he might want to count the three best balls of the five for each hole. This isn't an uncommon desire in some of the betting games in golf that occur every weekend on golf courses worldwide. In fact, there are so many variations on betting games in golf that books have been published to describe them.

Anyway, the formula was a bit complex but not too daunting. It involved creating an array formula. It took me about a half-hour to come up with the formula and I shipped his spreadsheet back to him.

Then I decided to be a little more thorough. I absolutely knew from past experience that the formula would get trashed at some point and I wouldn't remember it well enough to just "fix it." So, I decided to write a VB macro so my friend could just press a button and have the formula automatically repopulated - basically a safety net.

What a nightmare. The formula was valid but Excel refused to allow me to create the formula via Visual Basic. As with most technology, I presumed that if it didn't work it was my fault and I spent days trying to find the error I was making before discovering that this is a known issue with Excel. In fact, this issue has been known since at least 1999. The work-around is insane; one has to write a nonsense formula that fits the same structure of the desired formula and then perform text substitutions. In this particular case, Microsoft has arbitrarily limited the length of an array formula created with a macro to 255 characters - a limitation that doesn't exist for a formula manually entered on a spreadsheet.

This is one of the key problems with closed-source software. This problem is, most likely, easily fixed. However, Microsoft has not deigned to fix it. In the open-source world the frustrated users don't have had to spend years inventing kludgy work-arounds, instead they can just fix the underlying problem.

And for all this, Microsoft still has the nerve to charge outrageous prices for the right to use the software. It's so unfortunate that so many users still want to use Microsoft Office because they think it's the easier thing to do. We're well past time to move to a new model.