Bad Engineering

Despite my preference for R over Excel, I still use a spreadsheet for many applications. But I find spreadsheets are often misused, or poorly constructed, and can create work later that can exceed the time and effort they might have saved you in the beginning. One way to prevent this is to think database when you are spreadsheeting.

An Excel spreadsheet is not a database and shouldn’t be thought of one, even if the two words are used interchangeably by people like, say, upper management (if your organization is anything like mine). But if you are doing anything with a lot of rows or columns of data, it is always a good idea to construct the spreadsheet as if it were a database. This at least gives you a chance that you will be able to add or extract data from the spreadsheet.

For example, we routinely receive large spreadsheets of thousands of rows long that are, outside of the bounds of the spreadsheet format, almost useless. The addition of fancy titles, merged cells, subtotals, empty rows, and other spreadsheet junk that disrupts the data makes extracting the data tedious at best and nearly impossible at worst. It would have been so much more useful had the author made one sheet hold the data—one header row with unique field names and just the raw data below, one record per row—and one sheet holding the spreadsheet math to provide the summary and calculated information. This is actually easier to construct and doesn’t lock the data in the spreadsheet.  It is easy enough to export the raw data as a dbf or comma delimited file to work on it in a proper database program or just use the data without relying on the Excel structure.

I know there is a temptation there to add the spreadsheet junk.  Pretty colors, lines, comment blocks, font changes, etc. are fine for that sheet that does the calcs (even if all that is usually a waste of time), just don’t let the junk get in the way of the data.  That’s the important stuff.

What do you think?  Drop us a comment.

Do you have that thing that you always come back to, that you never seem to tire of, that always seems to have hidden depths and complexities that await exploring, that just makes you feel good? No, I ‘m not talking about your spouse (but if you were thinking about yours, congratulations). And no, not your Ferrari 599 either (you lucky bastard). Nor your HP 15C. Okay, maybe the 15 comes close.

I know, this is totally subjective and the answers are as varied as we all are. But for me, at least most of the time, it is . No not the shiny black stuff worn by the nubile young things on the Internet (which isn’t too bad in itself… smooth curves catching the light just so, hints of smooth…) sorry, seemed to have drifted off there for a second. Where was I? Oh, yeah, . The document preparation system. (Pronounced lah-tech or lay-tech).

is essentially a set of macros sitting on top of the venerable typesetting system, , invented by that computer master, Donald E. Knuth. No, it isn’t a word processor, and it shows its age by being almost GUI-free, but it is the thing to use to produce documents you can be proud of. All with a simple text editor and some markup commands that can be simple enough for a school child or complicated enough to keep the most nerdy of PhDs happy for years.

I’ve prepared three books and many papers and reports with and it was a pleasure to use and produced beautiful documents with little need for me to fiddle with margins, headers, page and section breaks, tables of contents, indexes, citations, etc., since most of these jobs are done automatically. You can, of course, customize the document to the nth degree, but most of the time the default settings are fine. And all the time you know what the program is doing, unlike M$ Word which I have to say drives me crazy.

And all you need is a decent text editing program and you’re in business. You’re source file is in plain old ASCII which means you can edit the file on virtually any computer and compile it there too–and get the same output whether you happen to be using a PC, a Mac, Linux, or pretty much any other system. And no worries about corrupted files. The source file isn’t touched during compilation, and when was the last time your text editor screwed up your ASCII file? Probably never.

Yes, the learning curve for can be steep–depending on what you’re trying to do–but it is so satisfying to learn and use. Especially compared to that pinacle of mediocrity that is Word. Better words than I can write about that comparison can be found here:

http://ricardo.ecn.wfu.edu/~cottrell/wp.html

And the following link discusses the beauty of :

http://nitens.org/taraborelli/latex

And I almost forgot one feature that stands above most others, the ability to typeset beautiful equations.  Though I gather the M$ equation editor is getting better, imagine just being able to write something like this:

\int_{0}^{1} x dx = \left[ \frac{1}{2}x^2 \right]_{0}^{1}
= \frac{1}{2}

and get something like this:

or this:

\gamma \equiv \frac{1}{\sqrt{1 - v^2/c^2}}

and get this:

Even WordPress here and WikiPedia use for formula setting.  So much easier than futzing with the M$ equation editor.

Am I being archaic and anachronistic? Sure. But it is better, a better way, and just more satisfying than almost any other thing on my computer. As a matter of fact, and R (see previous post) are the only things I need on my computer. Okay, a browser too. With these I can do anything I want. And all for free, I might add, though I do support the users group (www.tug.org) and its talented membership who make all this possible. I’d much rather give them my money rather than Micro$oft. I think they have enough.

So if you’re up to a challenge, give it a try and let us know what you think.