I was following these instructions (http://www.comfsm.fm/~dleeling/statistics/text5.html) and tried to calculated MODE. Steps to reproduce: 1. Copy these values to clipboard (CTRL +C) 18,19,19,20,20,21,21,21,21,22,22,22,22,23,23,24,24,25,25,26 2. Open Calc 3. Click on any cell 4. Type "=MODE(" and paste values + ")" 5. Press Enter Current behaviour: Extra "0" are added. Expected behaviour: No "0" is added.
Indeed, i see: =MODE(18,190,190,20,20,210,210,210,210,220,220,220,220,230,230,240,240,25,25,26 ) in the cell after doing what you described.
It acctually happens with any other funcions: SUM, AVERAGE...
But if you paste these values: 18;19;19;20;20;21;21;21;21;22;22;22;22;23;23;24;24;25;25;26 it will not happen? Since "," is decimal point in my local language settings, could it be that Calc is trying to "format" the number?
(In reply to Ljiljan from comment #3) > Since "," is decimal point in my local language settings, could it be that > Calc is trying to "format" the number? Exactly. This is indeed not a bug. Calc try to convert this strings to numbers by adding 0 before comma. Using correct function args separator (;) is the correct way.
Subject of this error was changed from: Calc change values when doing replace to: Calc change values when pasting values from clipboard But a better title would be: Calc change values when it finds a comma Because manually entering the comma-separated numbers also triggers the error.
"Calc try to convert", but not succeeding!! That's why Calc SHOULD NOT CHANGE A THING!! (Grrr.... ;-)
Well, this is stil a bug? :) final output of this conversion does not make sense... If only it does following: 18,19;0,19;0,2;0,2;0,21;0,21;0,21;0,210 "8,190,190,20,20,210,210,210" is not a number either.
It is exactly the same as when you wrote ,35 in a cell: Calc add a leading 0 and display 0,35. Here you have commas not preceded by 0 and Calc adds them.
When someone copies/pastes text from anywhere to another place, This input should be checked. If the receiving application cannot do a correct thing with it, it should not change ANYTHING to the copied text. It is up to the user to make sure that the copied/pasted text is correct. (and not to an assumption made by some developer!)
(In reply to GerardF from comment #8) > It is exactly the same as when you wrote ,35 in a cell: > Calc add a leading 0 and display 0,35. > Here you have commas not preceded by 0 and Calc adds them. This is correct behaviour, but when the text you entered contains 2 (or more) commas it should not change the text, and throw an error When entering '=1,2,3,4,5,6,7,8,9' in a cell without single quotes) Calc also 'decides' to change it to : '=1,20,30,40,50,60,7,8,9' which is WRONG
(In reply to Luuk from comment #9) > When someone copies/pastes text from anywhere to another place, > This input should be checked. > > If the receiving application cannot do a correct thing with it, > it should not change ANYTHING to the copied text. > > It is up to the user to make sure that the copied/pasted text is correct. > (and not to an assumption made by some developer!) Yeah, there's definitely a judgment call going on here; I'm reticent to impulsively pick one interpretation :-) (In reply to Luuk from comment #10) > (In reply to GerardF from comment #8) > > It is exactly the same as when you wrote ,35 in a cell: > > Calc add a leading 0 and display 0,35. > > Here you have commas not preceded by 0 and Calc adds them. that makes a lot of sense > This is correct behaviour, but when the text you entered contains 2 (or > more) commas it should not change the text, and throw an error That sounds like a plausible strategy. I think I'd also be okay with it adding explicit separators such as "18,19; 0,19; 0,5" etc. > When entering '=1,2,3,4,5,6,7,8,9' in a cell without single quotes) > Calc also 'decides' to change it to : '=1,20,30,40,50,60,7,8,9' which is > WRONG In the absence of any set parsing rules, I don't see it as being wrong. I agree that 1) There should be better documentation about HOW we parse cell input 2) We should be more explicit with the output (i.e. add separators), and 3) In some cases we should give a warning/error
Markus: thoughts on how we parse incoming values that include several delimiters?
(In reply to Robinson Tryon (qubit) from comment #11) > I think I'd also be okay with it > adding explicit separators such as "18,19; 0,19; 0,5" etc. I just chatted w/the Calc devs. One problem with adding explicit separators/delimiters here is that they aren't there in the original input. If there is no ocSep token in the token sequence, adding one in might break some other use-cases. In this particular example, the input goes through the formula compiler which means that it is interpreted as normal formula. Making changes at that level is very tricky, and has a high possibility of inadvertently introducing a number of regressions. Sticking this one in NEW. It would be great to see the Documentation Team provide some more information on this topic in the Calc manual.
(In reply to Robinson Tryon (qubit) from comment #13) > I just chatted w/the Calc devs. One problem with adding explicit > separators/delimiters here is that they aren't there in the original input. > If there is no ocSep token in the token sequence, adding one in might break > some other use-cases. Nothing should be added because there are multiple 'ocSep' characters in the pasted string. > > In this particular example, the input goes through the formula compiler > which means that it is interpreted as normal formula. Making changes at that > level is very tricky, and has a high possibility of inadvertently > introducing a number of regressions. > The input should not go through the formula compiler because there are multiple 'ocSep' characters in the pasted string. I think it should be pasted as 'text'. (no changes to the pasted text!)
(In reply to Luuk from comment #14) > (In reply to Robinson Tryon (qubit) from comment #13) > > > I just chatted w/the Calc devs. One problem with adding explicit > > separators/delimiters here is that they aren't there in the original input. > > If there is no ocSep token in the token sequence, adding one in might break > > some other use-cases. > > Nothing should be added because there are multiple 'ocSep' characters in the > pasted string. There is no ocSep. These are decimal separators in your locale. Change the ',' to ';' and it works as they are the separators in your locale. > > > > > In this particular example, the input goes through the formula compiler > > which means that it is interpreted as normal formula. Making changes at that > > level is very tricky, and has a high possibility of inadvertently > > introducing a number of regressions. > > > > The input should not go through the formula compiler because there are > multiple 'ocSep' characters in the pasted string. > > I think it should be pasted as 'text'. (no changes to the pasted text!) Just stop with that "paste" stuff. This has nothing to do with pasting. You get the same result if you type that character sequence. As soon as we start to parse that formula this goes wrong. After thinking about it even more I think that it is actual the correct behavior so I will close that bug. Don't use the decimal separator in a formula string unless you want the stuff being parsed as decimal number. There is nothing wrong with how we parse the formula.
(In reply to Markus Mohrhard from comment #15) > After thinking about it even more I think that it is actual the correct > behavior so I will close that bug. Don't use the decimal separator in a > formula string unless you want the stuff being parsed as decimal number. > There is nothing wrong with how we parse the formula. The Docs Team should verify that we're clear about this expected behavior in our documentation, so Whiteboard -> needsDocumentationReview