Is there a way to configure Libreoffice so that a cell referring to an empty cell also displays an empty cell?

Question

Suppose a Libreoffice spreadsheet involving just two cells, namely A1 and A2, has the string "abc" in cell A1 and "=A1" in cell A2.

    |---+------+
    |   |   A  |  (Showing formulas)
    |---+------+
    | 1 | abc  |
    | 2 | =A1  |
    |---+------+

Evidently cell A2 will then display the same content of cell A1, namely "abc", as below.

    |---+------+
    |   |   A  |  (Showing values)
    |---+------+
    | 1 | abc  |
    | 2 | abc  |
    |---+------+

At this point suppose that you delete the content of cell A1, so that A1 becomes empty. What happens to A2? Answer: A2 displays the number zero.

    |---+------+
    |   |   A  |  (Showing values)
    |---+------+
    | 1 |      |
    | 2 |   0  |
    |---+------+

I think this behaviour is unreasonable. I think A2 should also display nothing!

I thought I could change this by tweaking some configuration and I got as far as the somewhat obscure "Tools - Options - Formula - Details", but it doesn't quite do the job.

Here is a concrete example to support my argument that the standard behavior is indeed unreasonable. Given the spreadsheet:

    |---+------+------+------------------|
    |   |   A  |   B  |        C         | (Showing formulas)
    |---+------+------+------------------|
    | 1 |   2  |   4  | =average(A1:B1)  |
    | 2 | =A1  | =B1  | =average(A2:B2)  |
    |---+------+------+------------------|

which displays as

    |---+---+---+---|
    |   | A | B | C | (Showing values)
    |---+---+---+---|
    | 1 | 2 | 4 | 3 |
    | 2 | 2 | 4 | 3 |
    |---+---+---+---|

suppose that we delete A1. The table will then display

    |---+---+---+---|
    |   | A | B | C | (Showing values)
    |---+---+---+---|
    | 1 |   | 4 | 4 |
    | 2 | 0 | 4 | 2 |
    |---+---+---+---|

and I think there is no reason why C2 should display 2, as it does. I think it should have the same value as C1, namely 4. After all, line 2 is essentially trying to replicate line 1.

Question: Is there a way to configure Libreoffice so that a cell referring to an empty cell also displays an empty cell?

Answer 1

After studying a bit about user defined functions (gently being pushed to do so by @tohuwawohu), here is my solution, written in a language I'd have understood before knowing anything about user defined functions, and tested in version 6.0.7.3.

1. Via the menu bar, go to Tools - Macros - Organize Macros - LibreOffice Basic...

2. Click on the button marked Organizer...

3. On the little window, select the name of your current spreadsheet

4. Click on the button marked New..., and type in a name for your Module, say "MyModule" and click Ok

5. Back in the window "LibreOffice Basic Macro Organizer", click Edit

6. You are now in the IDE (Integrated development environment), where you will define your function. Begin by clicking anywhere in the big top right window to activate it.

7. Go to the last line and type in:

   Function id(x)
       id = x
   End Function
   

This will create a user-defined function called id, which is effectively the identity function.

This is it. Now, in order to test it, go back to your worksheet window (e.g. via the menu bar option Window) and type in the following

    |---+---------+---------+-----------------|
    |   |    A    |    B    |       C         | (Showing formulas)
    |---+---------+---------+-----------------|
    | 1 |       2 |       4 | =average(A1:B1) |
    | 2 | =id(A1) | =id(B1) | =average(A2:B2) |
    |---+---------+---------+-----------------|

You should be seeing

    |---+---+---+---|
    |   | A | B | C | (Showing values)
    |---+---+---+---|
    | 1 | 2 | 4 | 3 |
    | 2 | 2 | 4 | 3 |
    |---+---+---+---|

Now here is the most important part: delete cell A1. You will then see

    |---+---+---+---|
    |   | A | B | C | (Showing values)
    |---+---+---+---|
    | 1 |   | 4 | 4 |
    | 2 |   | 4 | 4 |
    |---+---+---+---|

which, I believe, is the expected behavior!!

---

Conclusion: when you type in =A1 in cell A2, you do not quite get what you have in A1. Libreoffice makes a guess (which I think it shouldn't) that when A1 is empty, what you really want in A2 is ZERO.

With =id(A1) (logically this is indistinguishable from =A1) I guess Libreoffice is somehow prevented from doing any funny interpretation and puts in A2 exactly what you have in A1, including the case that A1 is empty.

---

Quoting from "Predictably Irrational: The Hidden Forces That Shape Our Decisions ", by Dan Ariely:

“Zero has had a long history. The Babylonians invented the concept of zero; the ancient Greeks debated it in lofty terms (how could something be nothing?); the ancient Indian scholar Pingala paired Zero with the numeral 1 to get double digits; and both the Mayans and the Romans made Zero a part of their numeral systems. But Zero finally found its place around AD 498, when the Indian astronomer Aryabhatta sat up in bed one morning and exclaimed, "Sthanam sthanam dasa gunam" — which translates, roughly as, "place to place in ten times in value". With that, the idea of decimal based place value notion was born. Now Zero was on a roll: It spread to the Arab world, where it flourished; crossed the Iberian Peninsula to Europe (thanks to the Spanish Moors); got some tweaking from the Italians; and eventually sailed the Atlantic to the New World, where zero ultimately found plenty of employment (together with the digit 1) in a place called Silicon Valley.”

I would humbly add that spreadsheets admit something representing "nothingness" that is even more ethereal than zero itself: an empty cell!