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:

Image of Excel 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.

* * *

Share

Comments

Calculating with Incense and Spreadsheets — 2 Comments

  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
    EditGrid online spreadsheet =PASSED
    Evermore Office spreadsheet = FAILED
    GNUmeric open source spreadsheet = FAILED
    goBeProductive Suite = PASSED
    Google online spreadsheet = PASSED
    Lotus 1-2-3 = PASSED
    OpenOffice/StarOffice = PASSED
    Softmaker Planmaker = FAILED
    Thinkfree online spreadsheet = PASSED
    WordPerfect Office Quattro Pro = PASSED
    Zoho online spreadsheet = 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.

    Google online spreadsheet = PASSED

    Thinkfree online spreadsheet = PASSED

    Zoho online spreadsheet = PASSED

    GNUmeric open source spreadsheet = FAILED

    Evermore Office spreadsheet = FAILED

    For the ones that failed, it almost makes you wonder if they’ve pirated
    MSFT’s code!

    I no longer have WordPerfect or Lotus suites on my system, nor some of the others like Ability or Softmaker. Have you tried the test on them? Did Excel EVER get it right, perhaps in DOS?

    I just bought a Dell Axim, and I should have stayed with Palm… Outlook changed a whole bunch of things — I use it only for calendar and contacts. Is there a good alternative for that, and perhaps an alternative to Windows Mobile for the device?

    Thanks so much for a great book. I’ve been using OpenOffice for four years. Word won’t start on my computer because it conflicts with my iFeel mouse. I have to keep Word 2000 on my system just in case I need it, the last version the came close to working.

    Regards
    Joe Webb
    Harrisville, RI

Leave a Reply

Your email address will not be published. Required fields are marked *