Thanks SGIII.
Sorry for not being clear, my fault: I was referring to the user interface. The new version needs far more clicks to achieve the same thing, it has no inspector palettes, only an unmovable sidebar, and its interface elements are too big, made for the touch screen.
By large spreadsheets I did not mean number of data but rather number of tables and other elements on the screen. The display in other words. And I have a very big screen (5120x1440).
As to the VALUE function, thanks for pointing it out. Its name conflicts with what it does. It just tries to make a value from a string, rather than take the value of the cell. This is explicitly shown in the example given in the explanations.
Therefore this goes wrong:
well, obviously. So, while I thank you for pointing it out, unfortunately it does not help. I should have used a different name than VALUE.
And yes, reasonable compatibility with Excel is useful.
We're stuck in some very old model of spreadsheets, dating back to VisiCalc and Multiplan. There is no strict distinction between the characteristics of the value of a cell, the way in which that value is displayed on the screen, and the way in which it can be edited. There should be four aspects:
- the formula producing the value (which can be a literal)
- the value (and its type, i.e. integer, character string, date, enumeration, … )
- the display format
- the restrictions on typing if desired (so that for example if a cell has to be an e-mail, the syntax should be checked)
At present there is a lot of confusion, automatic conversion and ad hoc decisions.
Just a few minutes ago I documented some calculations for a friend. I added a column in which I showed how some rows were derived from other ones. For example that row 20 was the difference between row 14 and row 16, so I typed 14-16 into the explanation cell of row 20. This all worked fine until I typed 21-11. Numbers immediately converted that to "November 2021"! Fortunately I spotted it, then explicitly formatted the cells as text.
A similar problem exists with leading zeroes: my postcode is 01280. I always have to be very careful with our membership database, where a lot of people have such postcodes. Extracting the addresses into a spreadsheet must format the postcode column to text before pasting, or it all goes wrong.
I remember also a very bad incident in 1992: I used Excel (on a PowerBook 170) to store licence numbers for software. For some reason I needed to reinstall Mathematica (yes, the 40MB Powerbook did run Mathematica). But the licence number was something like 123-345-567-789 and Excel had looked at that, decided I must have forgotten to type the leading formula "=", inserted it for me and had neatly added up all the numbers.
Before we reach the mountains of Artificial Intelligence, we have to traverse the deserts of the half-witted devices.
