UI: Poll: how should LO calc react to format changes

English
, , , , , , ,
#1

if you change the formatting of cells, e.g. from ‘text’ to ‘number’ (or the other way around), it is currently evaluated with a delay, namely the next time an input is made into the cell. This ‘irritates’ users from time to time and leads to questions in forums and in ‘bugs’.

[edit b. 2021-05-21] to avoid misunderstandings, this question is about if changes of the type! of the cell format should be reflected in the content of the cell, not about changing 2.499 to 2.50 with formatting to 2 decimal places. the details of display of numeric values shouldn’t change the value. It is a question to avoid situations like in bug 98590 having two identical looking cells reacting and calculating differently, and to give the user control over his data, ‘i want this text, i get it text’. [/edit]

Theoretically there would be the possibilities:

A - an immediate reaction to a format change in the cell, with the possibility for the user to see that his changes were ‘accepted’, ‘worked’, and in case of an error (e.g. text cannot be calculated), to see it immediately (not sure, but having a chance), to assign it correctly to the cause, and if necessary to remove it with a simple ctrl-z and to continue working with undamaged data, or

B - that the user’s changes are not processed at first, so he thinks ‘it didn’t work’ and ‘blindly’ throws other changes after it (potentially setting up more traps), only to be surprised months or years later by changes or unwanted consequences of changes he can’t attribute, may not have even made himself, and can only correct with tedious debugging. And with a good chance to come across other landmines he or other people have installed before or in between?

[edit b. 2021-05-23] in the following paragraph is was completely mistaken … now it is like ‘B’, the developer arguments for ‘B’, and Excel does it like ‘B’, sorry, digital errors are always so ‘total errors’ if you run in one :frowning: [/edit]

Except:

  • ‘now it is like “A”’, and
  • a developer who argues vehemently for leaving it like that because he thinks it is ‘better’ for the users of e.g. large tables,
    i see one more point in favor of “A”, namely:
  • Excel does it that way (but Excel at least marks the differently formatted cells),

from the ‘usability’ point of view i see ‘A’ ‘B’ as irresponsible ‘sending blinded users on a slippery path’,

and i think you don’t have to go along with every nonsense Excel shows, to serve progress, correctness and! compatibility without having to make lazy compromises again and again, two optional modes ‘Excel compatible’ and ‘correct’ would make sense anyway.

So, question to the users (not the experts), how would you like to have it:

(A) - Format changes are processed immediately,
(B) - format changes are processed only at the next input to that cell, or
(C) - the possibility to determine this via an option (and to switch if necessary),
(Ca) - separately for each table as an option,

please no replies or discussions but just comments, and short, best just ‘A’, ‘B’, ‘C’ or ‘Ca’,

#2

As for me it works according to (A), edit your question to mention OS name and LO version.

Please do not use Add Answer but edit your original question to enhance the details of your question (answers are reserved for solutions to a problem on this Q&A site).

#3

Applying a number format never changes the cell content and should not. Never. Ever. Period.

#4

I may not have fully understood OP’s question. He argues about format change. A format is some request to display data in such and such form. Of course, it does not and must not change data. Only what is displayed is formatted according to the format code. If user chooses an incompatible format for the data, he gets #Err:xxx or similar. It is his own responsibility to fix format code.

When format code is changed, display update is immediate. And this is what everybody expects (with data kept intact). This is the present behaviour and must not change.

#5

I am comfortably as it is; but I vote for C, for inexperienced users. ¿Would you write the code?

@ajlittoz, I understand that is about Ctrl+1 - Numbers tab - Category.

#6

For the context of the “poll” - see tdf#98590 comments starting from 10. Also see this first question by @newbie-02 from 2019.

#7

To clarify the severe misinterpretation (as stated in the question) of the idea. The “please no replies or discussions but just comments, and short” as asked in the question makes no sense in this case, when one asks for voting on something that is worded in such a misleading way.

  1. The two things - format and data - are separate. The current behavior reflects this separation. There is data in cells (that may be textual or numeric), and there is formatting of the cells. Trying to merge the two concepts (as in “when you apply a format, you modify the data of the affected cells”) is conceptually wrong.
  2. One must realize that conversion of data is always destructive process, loosing some information. E.g., when you convert "1000.00 " to number, you get an integer 1000, and you loose information about number of decimals, presence of thousand separators or trailing spaces typed initially. In some cases such loss of information may be critical. On the other hand, having (sub)headings with strings looking as “numbers” is very useful, with formulas ignoring such “numbers” - and changing those strings to real numbers when doing a format change could change results of formulas.
  3. Number format is very often applied to large ranges, e.g. to whole columns. The data in such ranges can contain things like captions, separators, subtotals, etc. The ranges very often contain vast amount of data - tens and hundreds of thousands of rows. Other cells, including cells on other sheets, may depend on affected cells. It’s absolutely unrealistic to expect that user would be able to carefully inspect the affected range and all dependent areas after making such a wide-scale format change.
  4. On the other hand, data entry is usually targeted process, when one types something to a specific cell, and has much better chances to immediately see results of interpreting the entered string.
  5. There is a point of view, that cell number format must not affect the interpretation of the entered value at all (so not only affect the data upon format change, but also when one types), and an own specific setting to control entered text interpretation is needed. This has its logic, that would be even better separation between data and formatting; but that would need additional increase of complexity of the program. LibreOffice uses a kind of middle ground here, when the number format serves as a hint to help interpreter to guess which kind of data may be typed to the cell.
  6. And one must realize that the “category” of number format is artificial. Number format consists of four optional parts, covering both numeric and textual data; some format strings fit numeric data better; some are better for dates, or for texts - but there’s no fundamental incompatibility of any format string and a data type. Applying a “numeric” format to a text is not an error, but just correct use of its implicit fourth textual part; the same way, applying a “textual” format to a number is not an error. Just remove the grouping from the format dialog - the grouping that is mainly for user convenience - and you would not have the reason to expect one or another format to “change” the underlying data to this type or that.
#8

The “poll” states the difference between proposed and current variants as “an immediate reaction to a format change” vs “the user’s changes are not processed at first” - so the interpretation of the function is not what the function does and intended to do, but “I declare that applying number format is what I want it to do - changing data type - and I declare that it “delays” its effect currently”. No, it applies its effect immediately - showing the existing data using the new format string - and also adds a hint to the cell that will help interpreting the following input.

#9

There is a dedicated tool for data conversion. We probably need to emphasize its function in this regards - so maybe renaming from “text to columns” to something like “convert data” … but then it’s a familiar tool, named the same way in other suites …

#10

i’d really like to hear the ideas of some more people,
@mikekaganski, you are a coder, what we do in spreadsheets is similar, a - primitive - form of coding (i once heard in a lesson). in code you define a variable by type, and then put some value in it. think of the situation changing the typedef. from ‘string’ to ‘double’, and the compiler staying with ‘string’ - without telling you! - until the next change of the value.
my idea about the data in a spreadsheet: a combination of ‘what is in’, and ‘which type it is assigned to’, and then attributes around it from background colour to amount of decimals etc.
central thing for calculations: ‘what’ is in, and acc. ‘which’ type is it evaluated. and those two work in a tandem, express the users intention, and should stay consistent.
think you won’t like a compiler with ‘delayed evaluation’.
i know about ‘text to columns’, took some years to find it, and just try the POV of a normal user! he sees ‘text’ and wants that to happen.

#11

Ignoring the difference between the data and its representation, again. If you decide to take analogies from programming, then programming is not only about data types. E.g., there’s MVC paradigm separating model and its view. Just don’t try to use loose analogies - it’s not gonna work.

you are a coder

It’s so simple to call your opponent “a coder” and imply that this way, you diminish opponent’s PoV value. But besides being a LO dev, I was LO user for many years, and MSO user even longer before; and I was also a helpdesk and was training others using those. So I have much wider experience than you put it. Unlike many, my PoV is based on seeing it from multiple sides.

#12

hello @mikekaganski,
‘coder’ was in no way meant pejoratively, it was meant to say ‘you know how to deal with machines, bits and bytes, types and definitions, and that accurate reactions are expected’, and goes on as an argument that users of spreadsheets are entitled to the same claim, ‘what I tell the machine/sheet I want to see implemented’, if I say ‘sometime later by chance’, then so, but if I say ‘take this as text’, then I want that to be taken as text, now! LO calc also contains commands for a.l. ‘columns to numbers’? but users don’t find them and take what they have already seen, ctrl-1, ‘formatting’.
‘opponent’ and your POV: I see you as someone I can sharpen my thoughts on, not as an enemy. I appreciate your wide knowledge though it has gaps too (rounding). What I don’t like is how arrogant you are with people who have less knowledge than you and how rudely you defend - even wrong - points of view, I think in case of differences it can be discussed in a friendly way.

#13

if I say ‘take this as text’, then I want that to be taken as text, now!

The only problem is that you insist that user is using software correctly when they format things when they want their data to be “text”. When you format cells, you are NOT telling it “take this as text” - it’s the very MISCONCEPTION, the very USER ERROR that you defend by all means. It’s as if you said “good evening”, and kept telling people that you are right when you meant it to mean “I am hungry” (an analogy from natural languages i correct here, because interface is a language used to communicate with a software, and you keep telling that the word you use when communicating with Calc means not what it means, but what you want it to).

#14

What I don’t like is how arrogant you are with people who have less knowledge than you

You never provide a single example. I am never “arrogant” to people who have less knowledge - you are lying. I am very rude at you personally - and you did a lot to make this happen, starting to be rude without any reason in the very first question that you posted here - and I made a mistake to try to help you there.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.