Pages

Monday 28 August 2017

Spreadsheets

Back in 1983, I had a Sinclair Spectrum, and so did a friend of mine, Mike. We played various games, investigated the internals a bit, and often lunched togather.

One day, after lunch, Mike asked me to come up to his office. He showed me the office computer, an IBM PC, and it was running Lotus 123, which was by far the best speadsheet at the time. And he showed me what he'd done on this. There were inputs, formulae and outputs, and he showed me how changing the input data, led to the outputs changing. His fingers flew over the keyboard, and I quickly lost track of what he was doing, but one thing was clear to me - this was actually a program.

My friend Mike, who was totally a non-programmer, had written a program, and he was obviously very proud and pleased with what he'd done. On the basis of this, I thought that a *lot* of people would want Lotus 123, and the IBM PC to run it on. And on that basis, I acquired one for myself, brought it home, and started writing software on it - that was how S & S Enterprises (later renamed S & S International, the Dr Solomon's Software) got started. Our first product was a £ sign for the spreadsheet - yes, the UK version of Lotus 123 could not do a pound sign. We sold it mail order for £10, and the business took off like a rocket.

But, back to spreadsheets. What we have now, is millions and millions of people writing programs. They call them "spreadsheets", of course, but they are actually programs, and the usual rules about programming and programs apply. Which, of course, most of these people are unaware of.

Accountants, lawyers, doctors and architects, all happily writing programs. It makes my toes curl.

Because 88% of these speadsheets have errors.

I have no idea what to do about this. If you google, you'll find articles like "17 Common Spreadsheet Mistakes" and "Stupid errors in spreadsheets could lead to Britain's next corporate disaster". Google will turn up some guidance, such as "10 Common Spreadsheet Mistakes You're Probably Making" (which makes me want to grab the guy who wrote that headline and explain that you Do Not Capitalise Every Word in a sentence) and "12 COMMON SPREADSHEET ERRORS".

But none of that really helps, because people who aren't programmers, haven't yet discovered one of programming's important lessons - everyone makes mistakes. And the more unreadable your code is, the more likely you are to get things wrong (spreadsheets are about as unreadable as you can get, with variable names like ad43 and z28 iinstead of $vatrate and $discount). The people creating these spreadsheets are blissfully unaware of the possibility of error ("it must be right, I did it myself"), or how to go seek for errors. They've not heard of edge conditions, or intermediate results. They won't hand-calculate a few outputs, or try unusual inputs.

And that's before we start thinking about macros, which are written in a language called "Visual Basic for Applications".

How many people setting up VBA macros (they call them macros, they're actually programs, but Microsoft doesn't want to frighten people) have had any VBA training?

Gordelpus.



4 comments:

  1. TEN QUID for a £ !!! OMG! How on earth are we going to afford a Euro then !! :)

    ReplyDelete
  2. Not so long ago, in the closing years of the final decade of the last century I did some work for a national QUANGO. They performed their accounts analysis via spreadsheets - I think it was Supercalc. When it came to performing year end consolidations of the accounts of all the regions, Supercalc running on a Compaq PC couldn't cope. Not enough memory. In those days, the spec could be enhanced by adding either extended or expanded RAM but it wasn't just a case of adding a RAM chip in a slot. A Compaq proprietary expansion board was required - cost, about £3k!

    ReplyDelete
  3. You could have got a non-Compaq board for a lot less; these boards were being sold by loads of people.

    ReplyDelete