Spreadsheet use is a valuable technical skill, can teach you to think smarter and constitutes “real” computer programming.
Time for a reversal. I’m going to praise spreadsheet users. I’ve never spoken ill of the technology mind you, and history is on the spread sheet’s side, as it is oft cited as the killer app that got businesses to adopt computing in the 1980’s, (in fact I think the spreadsheet is the original killer app), but I have had the arrogance to stratify “computer people,” putting “people who use Excel a lot” into some barony just slightly above the peasants and distinctly below “real” programmers. I have similarly knocked spreadsheet enthusiasts who build “databases” in my rush to ensure that you took me seriously when I said real databases are really really complicated, (and they are).
Well, not this week. This week its all about how spreadsheet use is a valuable technical skill, can teach you to think smarter and, dare I say, constitutes “real” computer programming after all. Of course, serious spreadsheet writers already knew all this.
The motivation to come out with this comes as a response to an all too common big business horror story: the million dollar spreadsheet error. Consider this, this and that, to name just a few. Some of the causes might sound like a joke. A “copy and paste error” sounds absolutely amateurish, (and maybe it is), but it would be folly for a software engineer to take the moral high ground over such a thing – I can say that copy-paste errors are a huge source of bugs and other headaches for software engineers too, amateurish or not.
Spreadsheets are serious business. There probably isn’t a standard business culture around spreadsheets, but I would hazard a guess that the software engineer’s ghettoization of the spread sheet doesn’t make things any better. The fact that spreadsheets are dismissed as some tool “anybody can use” is what contributes to important data falling into disrepair – nobody fully respects the level of technical skill required to avoid such mistakes. If spreadsheets are seen as software, then it is easier to create a culture around the idea of testing and debugging them. Of course, it doesn’t help that everybody under the sun wants to, (and does), list “Excel” as a skill on their resume. The software engineer, (i.e. the person who can help you write better spreadsheets), disdains this popular accessibility and so downplays the similarity between software bugs and spreadsheet bugs. It can be a vicious cycle.
Of course, exactly how spreadsheet writing equates to computer programming isn’t obvious, both to layman and engineer. Two of the largest paradigms of programming languages are imperative and functional, and if you were to draw only one line in the sand over which us super-geeks could argue, this would probably be the one to draw. If I were to give you a definition of these paradigms that did them justice, it would be among the worst of my rambling tangents. Instead I’ll give the quickest “what you need to know” explanation.
First, what you probably think of as computer programming is specifically imperative: you think of programming as a series of instructions. A program does something, and the programmer uses language to define the things to be done. Functional programming is functional in the “mathematic function” sense, (f(x) = 2x + 5, that sort of thing). The program is a list of definitions. The programmer uses language to define relationships.
Spreadsheets are functional programming languages. This isn’t a metaphor, I mean it in actual fact. Each cell is either a value that you state, (call these values input), or a function of other cells, (call these functions the program). In a good spreadsheet you can add or change values, (new receipts, new bills), so that you can update your understanding of your situation, (new revenue estimate). You have input, you have your “program”, and you have output, and you can test it. If you leave your sums and other functions in the table, but change the “input” cells, you are effectively testing your code with different values. If you are a downtrodden spreadsheet enthusiast who felt something deeper was going on – you were right.
Spreadsheets also demonstrate what I’m calling the “elegant inelegance” phenomenon. Spreadsheets are so much more intuitive to the average user that they easily qualify as elegant, where elegant is a nice word for something that is simple and clear. However, “real” functional languages are immensely more succinct in their expression. Spreadsheets take up pages of visual space to understand, where functional languages can describe the same relationships with much terser syntax. For this point of view, the spreadsheet is an unwieldy thing, and much more obtuse to program with. This tension is common in a lot of the things we do; the kind of elegance that makes something accessible to all people is also what causes it to appear inelegant to experts.
Ultimately, I don’t have some profound solution to the million-dollar-spreadsheet-error, other than to repeat the need for some sort of culture change. Instead, I leave you this week just hoping I’ve helped you understand something commonplace in a more interesting light.