The Good, the Bad and the GUI

>There is no excuse for a spreadsheet quietly taking a never-assigned cell as zero, but indeed it does. WHAT THE HELL WERE THESE PEOPLE SMOKING?

I think the head scratching about Excel's (and probably other spreadsheets) behavior on empty cells is misguided. Yes, if you have a computer scientist mindset then it might seem very mathematically satisfying to have strict rigorous logic around empty cells but we're getting into 3-valued NULL handling like databases. That type of extra logic checking is misplaced considering the origins of spreadsheets (e.g. Dan Bricklin's VisiCalc) as a business computation tool instead of a GUI for an E.F. Codd relational db.

If a particular power user finds the typical "null" cell behavior to be "wrong", he can add formulas to his cells such as:

=IF(ISBLANK(C3), NA(), C3)

... such that #NA# (Excel's quasi "null") propagates throughout any SUM() and other calculations.

However, imagine if the situation was reversed and we had an alternate universe where Excel propagated empty cells as #NA# instead of zero to satisfy the logic of computer scientists. We'd inevitably have:

1) Microsoft adds a button wizard on the toolbar to bulk fill "null" cells with 0.

2) User forums with power-users trading VBA macros to fill in zeros of empty cells. "Ugh! How do I get rid of all these useless #NA# littering my spreadsheet???"

3) Microsoft adds an user option checkbox to "treat empty cells as zero instead of #NA#" -- which everyone ends up activating as a defacto setting. This cancels out the strict null handling the computer scientists were imposing on the spreadsheet!

I contend this alternate universe scenario is much worse for business users of Excel. In other words, you can't "force fit" the concept of NULL as a default where it is not natural. The end users will just work their way around it.

Actually, I wouldn't even throw an error. It's quite intuitive for the user when "sum" and "product" apply only to non-empty cells, and empty cells are ignored. If all cells are empty, the return value should be the identity of the function, i.e. 0 for sum and 1 for product. Also it's nice to have a function "count" that counts non-empty cells, with identity value 0.

    A1 = 1, A2 = blank, A3 = 3
    sum(A1:A3) = 4
    product(A1:A3) = 3
    count(A1:A3) = 2
    average(A1:A3) = sum(A1:A3)/count(A1:A3) = 2
    geometric_mean(A1:A3) = product(A1:A3)**(1/count(A1:A3)) = sqrt(3)

Note that the last line wouldn't work under your proposal, because the identity for "product" isn't the same as the identity for "count". I think the user would be massively confused by an error like "using conflicting identity values for cell A2", and would prefer a spreadsheet that just gave them the damn geometric mean.

Yeah, hate to break it to the OP, because God knows I speak against the use of spreadsheets whenever possible...but the most common use-case for a spreadsheet is not as part of a reproducible, automated data pipeline. Many times, it's hand-entered data entry, with the goal of making one of the pre-baked Excel visualizations. For users who want type-checking, there's Access, which is more attuned to handling non-trivial datasets.

> Many times, it's hand-entered data entry

Yes, many companies pays good money for people to update those Excel sheets.

Me? I see almost every single Excel sheet that is updated over more than a week as a possible sale. But I'm not replacing Excel spreadsheets full time, at least not yet : )

I've learned to love Excel. Despite the horribleness mentioned and the seemingly intractable mess people make when trying to codify business processes with it, its still the very best way I've found for the end user to tell me what they want the "real" program to do when I finally write it for them. It forces them to stop thinking about specs in a genie-in-a-lamp way and start thinking about how an actual machine might make it happen. They glimpse the programmer's world through a glass darkly.