# Calculating with Incense and Spreadsheets

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.

* * *

Those students who have been through itil certification as well as ccna, still strive to get an mcsa certification. They must visit once actual tests website to get maximum results and details.

* * *

1. Excel Error that OOo Handles Just Fine

The following is an email comment from drjoewebb at drjoesblog.com (You can read the entire post and comments at “See Excel Error that OOo Handles Just Fine” ijn OOoForum.org, the OpenOffice.org forum):

I ran that spreadsheet in other programs, and this is what i got

602PC Suite = PASSED
Ability Office = FAILED
GNUmeric open source spreadsheet = FAILED
goBeProductive Suite = PASSED
Lotus 1-2-3 = PASSED
OpenOffice/StarOffice = PASSED
Softmaker Planmaker = FAILED
WordPerfect Office Quattro Pro = PASSED

including excel, 9 passed and 5 failed

Gnumeric (and Excel?) sum up dates, but they
refuse to sum up booleans. I tested with Gnumeric. It seems that this
program is aware of booleans. They show TRUE/FALSE with any numeric
format. Calc shows 0 or 1 with any numeric format but “Boolean”. So
“Boolean” is just a number format in Calc, whereas Gnumeric does not
know such a number format at all.

Gnumeric boolean is not equal to number:

=1=True() –>FALSE

=0=False() –>FALSE

until you convert

=1=N(True()) –>TRUE

=1=N(False()) –>TRUE

Calc:

=1=True() –>TRUE

=0=False() –>TRUE

2. Read and loved the book. Have been playing around with Ubuntu more aggressively since I read it, and enjoying it greatly.

Regarding the spreadsheet in your book (with the TRUE and FALSE statements) that Excel miscalculates, I have found that other products have failed the test as well.

GNUmeric open source spreadsheet = FAILED