It should come as no surprise to learn that the inventors of the spreadsheet and the first companies to market spreadsheet software were refugees from the Sixties counterculture. How often have you looked at a spreadsheet and thought, what were they smoking?
Before computers, paper ledgers were trusted because they weren’t so complicated that you couldn’t recalculate them by hand. But spreadsheets are far more complex, especially ones created in Excel that use multiple worksheets. The truth is a formula you can plug into a spreadsheet cell and never see again, nor question. Rows and columns of numbers seem accurate — but who really knows? You have to trust the spreadsheet maker and the program that made it, including its automatic formulas. But as spreadsheets proliferate through a company without any means of updating their information, they become multiple versions of the truth.
You want truth in a formula? Consider this spreadsheet:
Truth depends on what formula you use. The cell range A1:A3 contains Boolean values (TRUE or FALSE). Add them up one way you get 2; add them up with the SUM function and you get 0.
The formula in cell A5 uses the addition operator. The sum of these three cells is 2. The conclusion: Excel treats TRUE as 1 and treats FALSE as 0. Except that it doesn’t: the formula in cell A6 uses Excel’s SUM function. In this case, the sum of these three cells is 0.
Note that OpenOffice.org Calc, an alternative, provides the correct answer — 2.
OpenOffice.org Calc, part of the free OpenOffice.org suite, offers the same set of analysis and graphic tools as Excel. OOo Calc runs on Windows, Linux, Sun Solaris, and Mac OS X, and can open and save spreadsheets in all Microsoft Office formats as well as the standard XML format.
Microsoft grudgingly accepts the fact that there are alternatives to Excel and lets you save worksheets and entire workbooks into some of the more popular formats with the formulas and calculations, sometimes even the formatting. Of course, every time you do this, Excel warns you that you may lose something in the process. The WK4, WK3, and FM3 formats for Lotus 1-2-3 work fine, but beware of Microsoft’s XML: You can save the entire workbook in this format, but you lose any charts and graphs, macro sheets, custom views, drawn object layers, outlines, scenarios, shared workbook information, Visual Basic projects, and user-defined function categories. You also can’t save password-protected worksheets. Good luck with that.
* * *
* * *