unprecise - switching - evaluation and results when formatting a cell as text

[newbie-02]

hi all,

imho a spreadsheet should be clear, precise and produce unique results,

(unless you work with random functions intentionally)

calc doesn’t, whenever you change the format of a cell with a formula from number to text and vice-versa it produces irritating results,

i’d like to see your comments before filing that as a bug.

simple sample:

enter “1” in A1, all values without the quotation marks!

enter “=A1” in B1,

enter “=2*B1” in C1,

you’ll see 1 | 1 | 2 in the row,

change format of B1 to text,

you’ll see the result changed in only one little point, the “1” in B1 is now ‘left aligned’, the cell shows the result! of the calculation formatted as text,

imho the error starts here, a cell formatted as text shall show text, a programmer told me “no, it’s your fault, you should explicitly change the content to text by adding a apostroph (’=A1)”, thus she insists that showing “1” in B1 is correct,

if neccessary i could possibly live with that, i have difficulties to accept the next step,

in the above sheet enter the cell B1 and edit the content !!!without changing it!!!, e.g. insert a space and delete it while in edit mode,

after pressing enter the sheet will show changed content:

1 | =A1 | #VALUE!

while the formulas in the cells and their formatting are unchanged!!! cell B1 changes to show the text! of the formula instead of evaluating it,

that is a fault against the idea of the programmer, and in total it’s certainly a fault having different results for the same input.

going back (formatting B2 as number and after that doing a ‘no-changing-edit’) will produce the same irritation, between the two steps the shown “=A1” in B1 is contradictionary to the formatting,

the behaviour is in plenty (all?) old versions of calc, it’s still in ver. 6.3.0.0 alpha0,

imho it’s based on ‘when and what’ is to be recalculated after changes, either you do! recalculation after changing the format, or you can omit it because the result won’t change, but then it shouldn’t change later on,

as one can see from the behaviour of C1 the evaluation is! important, as it does! affect the result of calculations …

pls. tell what you think about it …

regards

newbie-02

[mikekaganski]

No. It is perfectly normal, and is based on formatting being different from conversion.

No - absolutely no - reformatting of any cell should ever change the value stored in that cell. You may get different visual output of that value based on applied format (if that format is applicable at all to the value type); but if a cell had a number in it, it should still have the number after applying a different format.

But when user inputs anything into a cell - that is the point at which software should process the input, and interpret it according to user’s intent. And the intent is hinted by the format pre-set to the cell into which the input is being done. If a cell is pre-formatted as text, then any following input must be treated as text, regardless if the string could be interpreted as a date/time, or a number, or a formula, or whatever - if there was another format pre-applied.

And after the input has been processed, and the textual value has been saved, no further change of cell format should again change the stored value (e.g., to number or formula).

A “no-change-edit” is just another variant of input. So it’s correct for the program to take the edited (unchanged) string and interpret it anew.

And for conversion purposes, there is specific Data→Text to Columns tool. Also see this FAQ.

[newbie-02]

@mikekaganski

it’s hard for me to follow that philosophy. in my opinion setting the format of a cell is nearly the same as changing the input, the user applies another ‘will’ to what is in that cell.

nothing against keeping the value still stored, but if i have a cell with anything in it and want it displayed / treated in any special way i’d like the program / sheet to follow my intention, that’s easy and intuitive to understand.

your philosophy makes the result dependent on things which are hidden and not in any way ‘checkable’ to the user, if you do input after formatting you’ll get one result, with formatting after input you get another result.

recalculating and changing the display after changing the format would be easy to understand, and i don’t get the point why you think it’s a problem.

‘data → text to columns’ does something totally different, it replaces the formula by it’s result, similar to strg-shift-v, if i want that i can use it, but i want to see the formula.

[mikekaganski]

No, setting the format of a cell is not nearly the same as changing the input. Spreadsheets traditionally have distinction of data and its representation. Numeric, date, textual formats is just a way to tell software how to show you the data, not what the data is.

Spreadsheets are created with scope of large amount of heterogeneous data. It’s usually some tables consisting of columns, with possibly several headings, potentially hundreds of thousands of rows, where some rows are textual (like section captions), some are subtotals formulas… they are operated by users (not programmers) - so as much non-destructive operations are required as possible.

And users might (and do) re-format their data in much massive operations than inputting the data. Users might select whole column - end even several whole columns; and change formatting to represent the data differently. In this process, having several cells changing not only formatting, but also contents would likely

[mikekaganski]

go unnoticed. Formatting (=defining representation) actions must not be destructive to data.

On the other hand, input is much more supervised operation. It either happens per-cell, or even if it’s done semi-automatically, the import results are usually checked. This operation is very different from representation.

It’s wrong to tread formatting as defining data. E.g., if one wants to only see 2 decimals, it doesn’t mean that other decimals are necessarily irrelevant for calculations. So formatting to 2 decimals must not truncate data. It’s wrong to think that if user formats a column as BOOLEAN, the data there must be reduced to 0s and 1s: a 3.1415 in a cell must stay there, despite user asked the system to simply show if the cell has non-0 value by using convenient formatting.

Formatting is applied using styles; conditional formatting; formulas; … so thinking that formatting might be equivalent to defining data type would just make most but simplest operations impossible.

[newbie-02]

@mikekaganski

being unsatisfied with your answer i’d look around,

changing the formatting of a cell from number to boolean value immediately changes the display,

typing text in a cell formatted as boolean displays the text while keeping the formatting,

input of a number changes display and! format,

doesn’t look consistent to me … and is - in display - another behaviour as applied when changing formula to text. the value is still kept, but in this case the display is! changed acc. to what the user did.

it is! difficult to deal with different formats, to give users a chance to understand what’s happening on their screens it’s - imho - not a good idea to keep and evaluate values in a cell which are not visible or touchable to the user.

nor changing cell format without notice …

wysiwyg was easier.

reg.

b.

[mikekaganski]

Formatting a cell as BOOLEAN tells that following input MUST be treated as a number (and be shown as TRUE/FALSE) if possible; otherwise as text.

Formatting a cell as text tells that following input MUST be treated as text if possible (and surprisingly, it’s always possible to treat an input text as text).

The two are perfectly consistent.

There are more complex formats; so you are simply looking at the very basic use cases, which looks easy to you - but spreadsheet software has developed to allow for very complex solutions of a real-world data. And it requires a learning curve.

[newbie-02]

@mikekaganski

i think we can agree to disagree,

my idea: a user wants consistent results, thus the sheet should follow each of his input,

your idea: protect users from unintentional changes,

but by the way you block intentional changes as well and produce hidden properties of cells,

afais your idea is not coherently realized in calc, it is! difficult to find a good idea, i’ll think about it …

reg.

b.

[newbie-02]

@mikekaganski
i had to think about this: “There are more complex formats; so you are simply looking at the very basic use cases, which looks easy to you - but spreadsheet software has developed to allow for very complex solutions of a real-world data. And it requires a learning curve.”
thus you think it’s ok to handle simple things in a ‘irritating’ manner because something is designed to handle more complex things? you’d like your car doing things by it’s own that are ‘irritating’ to you as the driver because the plenty computers in the car are designed to do ‘more complex things’?
for me: no, i’m used to brakes that brake when i! want it, steering to turn the direction i! want, and airbags to reduce their activity to real heavy impacts, but then they shall! work. “sometimes they do and sometimes they don’t” is not sufficient to me.

[mikekaganski]

Oh - you of course could invent just any analogy - so that using “proof by analogy” logical fallacy.

But a better analogy would be to think why, when you drive, you have to keep on your side of the road, and not on the opposite side; why you need to follow the road signs; and why your car has limited your engine power using something in its computer (which could be irritating, and drive Wolksvagen to do funny things to workaround that - until you realize that there are more complex things behind the decisions that led to those rules, those signs pot there, and those regulations imposed upon car manufacturers). This analogy is imperfect, too. But our mind is good at forgetting; and we don’t remember how many things were unnatural to us when we learned walking; speaking; writing; using mouse; and then - when we already have some background - we need to learn something new, and that similarly feels “unnatural” at first - we draw incorrect analogies.

[newbie-02]

sorry @all, this discussion is too long …

my philosophy: user input shall be evaluated at once, esp. faults should show up in a timely manner,

Mike’s: as long as possible the software should try to protect the user against his faults,

fine as long as it works,

backdrafts: it puts a large gap between source and handling of faults showing up lateron, and it produces ‘irritating’ behaviour of the sheets which may affect the users lateron …

and that the ability of users to do complex work with sheets is hindered once simple things become unneccessary irritating with hidden properties or ‘features’ of cells …

my impression is that plenty of the questions in ask.libreoffice result from things like this (i’d call them traps or mines or wrong complexity),

i don’t go for: “it’s good to be able to learn difficult things, thus it’s
good to make things difficult to keep us learning” nor similar ideas

our points are clear, i’d like to hear from others what they think about it.