The Good, the Bad and the GUI

&gt;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?<p>I think the head scratching about Excel&#x27;s (and probably other spreadsheets) behavior on <i>empty cells</i> 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&#x27;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&#x27;s VisiCalc) as a <i>business computation</i> tool instead of a GUI for an E.F. Codd relational db.<p>If a particular power user finds the typical &quot;null&quot; cell behavior to be &quot;wrong&quot;, he can add formulas to his cells such as:<p>=IF(ISBLANK(C3), NA(), C3)<p>... such that #NA# (Excel&#x27;s quasi &quot;null&quot;) propagates throughout any SUM() and other calculations.<p>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&#x27;d inevitably have:<p>1) Microsoft adds a button wizard on the toolbar to bulk fill &quot;null&quot; cells with 0.<p>2) User forums with power-users trading VBA macros to fill in zeros of empty cells. <i>&quot;Ugh! How do I get rid of all these useless #NA# littering my spreadsheet???&quot;</i><p>3) Microsoft adds an user option checkbox to &quot;treat empty cells as zero instead of #NA#&quot; -- which everyone ends up activating as a defacto setting. This cancels out the strict null handling the computer scientists were imposing on the spreadsheet!<p>I contend this alternate universe scenario is much worse for business users of Excel. In other words, you can&#x27;t &quot;force fit&quot; the concept of NULL as a default where it is not natural. The end users will just work their way around it.

I get it, I really do. But I&#x27;m a programmer.<p>Spreadsheets are the dominant end-user-programmable tool in existence. Much of what people user them for is horrifying to programmers who know better.<p>But if spreadsheets actually did the &quot;right&quot; thing, they would never have been so widely adopted in the first place. That&#x27;s an unprovable assertion on my part, but I strongly suspect it&#x27;s true. I think it&#x27;s a clear case of Worse is Better[0].<p>[0] <a href="https://en.wikipedia.org/wiki/Worse_is_better" rel="nofollow">https:&#x2F;&#x2F;en.wikipedia.org&#x2F;wiki&#x2F;Worse_is_better</a>

I&#x27;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&#x27;ve found for the end user to tell me what they want the &quot;real&quot; 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&#x27;s world through a glass darkly.

This should be in lesson 1 of any programming course.<p><pre><code> &gt; As for &quot;the developer is supposed to implement it&quot;, &gt; next week I&#x27;ll be giving my annual ethics lecture &gt; and I&#x27;ll be pointing out to students that the codes &gt; of practice of the various professional societies &gt; all agree that your duty goes beyond simply doing &gt; what you are told. &gt; &gt; If you are told to write consumer software that &gt; gets its sums wrong, you should not do it.</code></pre>

This is a cultural issue. Most business I know see a program telling you data is invalid just means the program is annoying and getting in the way.<p>They want the program to just assume you know what you are doing.<p>This is what they want, so this is what they get.

Back in the mid 1980&#x27;s I used software by Javelin software that could be used to serve the same purpose as a spreadsheet. Javelin was different in that one wrote rules based on variables not cells. It was much easier to avoid the errors that so often lurk hidden beneath the visible cells of a spreadsheet.<p>Unfortunately, I never met another user of the software and the company eventually went nowhere. The spreadsheet was a concept very easy for bookkeepers, accountants and other non-programmers to understand. Not so much with (the better) Javelin.<p>Here is an add for the software from 1985: <a href="http://www.thecomputerarchive.com/archive/Software/Applications/Javelin%20Plus%20(1987).pdf" rel="nofollow">http:&#x2F;&#x2F;www.thecomputerarchive.com&#x2F;archive&#x2F;Software&#x2F;Applicati...</a>

In every language I know there is a set if conventions around Null values, not set values, true, false and zero<p>We have only had the concept of zero for a thousand years and null for about fifty. Expecting there to be a common way of handling this in all domains and industries is a bit much and expecting it to be handled the way the OP prefers is highly unlikely (although I do agree with their opinion)<p>If the convention in accountancy is BLANK == 0 then that&#x27;s their convention. Implement it using a single method that&#x27;s nice and easy to instrument and discover for later on, perhaps raising warnings out to the reports produced.<p>Leave the worrying over not set values for when Unicode and UTC handling in excel is brought up :-)

I continue to be terrified at how spreadsheets are used in business applications. Squashing data that shouldn&#x27;t be in tables into tables &quot;so you can work with them&quot; was the worst thing we ever taught non-programmers to do. We should have taught them how to program instead.<p>(you can get close to programming in a spreadsheet if you know what you&#x27;re doing with Insert &gt; Name &gt; Create, and one day I&#x27;d like to see a spreadsheet that lets you write your macro functions in Python rather than VB, but nobody is taught to use Excel that way except people who also program.)

In the posters variant of a spreadsheet where a blank value is invalid, how would the common practice of SUM(column A) be handled, where column A has an unknown number of rows (that keeps on being added to)?<p>Require all formulas that refer to column A be continually changed to reference the true amount of rows? Stop at the first blank row? Stop at the last blank row? It seems more confusing to define rules for that case than to assume &quot;undefined values are zero&quot;.

1. Has anyone ever audited a non-trivial computer program and found it to be free of errors? (I&#x27;m sure I&#x27;ve created trivial spreadsheets with no errors.)<p>2. What if spreadsheets started with 0s in every cell, would this &quot;solve&quot; the problem? I don&#x27;t think so, and yet it would address this argument.

I was particularly impressed by this detail further down the conversation:<p>Let A1 = 1, A2 = blank, A3 = 3<p>PRODUCT(A1:A3) is 1<p>A1 * A2 * A3 is 0

That appalled user would have a heart attack at Awk, a language in which a previously unseen variable serves as zero or an empty string.<p><pre><code> count++ # if count doesn&#x27;t exist, it becomes 1 </code></pre> A previously unseen array reference materializes automatically too:<p><pre><code> count[$1]++ </code></pre> (I think this is garbage too, but it leads to throwaway programs that are very concise. If you write anything large, it&#x27;s going to bite you on the butt:<p><pre><code> mispelled[foo] += bar; # wee</code></pre>

I personally would prefer a spreadsheet that treated null as the identity for whatever function it was acting as an input to (0 for addition, 1 for multiplication, e for exponentiation, Identity matrix for matrix multiplication, etc.), only throwing an error when a null does not resolve to a single value (i.e. the same cell is used for both addition and multiplication). But that would apparently require me to smoke some stuff.

I think the right thing might be, &quot;default empty to a <i>tainted</i> zero&quot;, and then propagate and display taint. That gives immediate feedback and lets you get some sense of things playing with an incomplete spreadsheet but won&#x27;t silently give you bad data that you think is good.