Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet?

[Lupp]

Posters sometimes include well formatted (with the pre-code tool) Calc formulas with their posts which they claim to work.

If I copy such a formula and paste it into a cell of my sheet, I sometimes get one of the mentioned errors, independent of the changed context/environment.

What is the reason? How can I avoid this - for me if pasting and for others copying formulas posted by me.?

[Lupp]

For a long time localization (“l10n”) affected formulas only in two ways:

1. The function names. (Users interested in international cooperation chose ‘Use English function names’ therefore.)
2. The decimal separator used in numeric constants. (Just a plague since ISO accepted the alternatives, no simple workaround I would know of.)

A long time ago, l10n spillt over concerning now also the parameter delimiter: In locales not using the comma as the decimal separator, the comma replaced the semicolon as the default parameter delimiter for functions. Very bad. Lots of formulas, also those not containing numeric constants, which formerly were exchangeable globally, are no longer, therefore.

What to do?

• Generally set ‘Use English function names’ under ‘Tools’ > ‘Options’ > ‘LibreOffice Calc’ > ‘Formula’ > ‘Formula Options’.

  You will see that the few English terms you need to know then aren’t a problem at all. You may also use an English UI-language to be better prepared for international cooperation. (I do so despite being a German in Germany.)

• Generally enter a semicolon into the control right of ‘Tools’ > ‘Options’ > ‘LibreOffice Calc’ > ‘Formula’ > ‘Separators’ > ‘Function:’.

  Your Calc will then always display formulas using this parameter separator, and you can copy these formulas for global export without causing problems for those importing the formulas into their sheets. The semicolon is still accepted even if the locale uses the comma. Automatic conversion should take place for the view. Please, please, please, developers, don’t change that!
  The persistent format [file] still uses the semicolon anyway here due to mandatory specification and so does the .Formula property of cells accessible via the API from user code.

Application cases

• You want to paste a formula and get the problem:

  Thoroughly rectify the formula manually in the appropriate way with respect to your UI and locale settings.
  (If complicated / frequent:) Paste the formulas as text first and rework them with the help of Find & Replace.

• You want to post a formula for use/testing" by others:

  Accept the advice above concerning English function names and overriding the comma as the parameter delimiter by a semicolon if needed. No specific measures needed then in any single case - except, probably, a hint for readers to regard the decimal separator if non-integer numeric constants are contained.

A few final caveats

• Localization beyond the absolutely necessary is a way to hell concerning cooperation. It should be abandoned.
• Never use USA date formats except for helping to get rid of them. Always use 4-digit-year in dates.
• Do not believe the point is the “international” decimal separator. The majority of locales uses the comma in this role.

[jimk]

You should mark this answer as correct. I think you may have enough karma to accept your own answer.

[jimk]

The information is good, but it is somewhat long and not formatted all that well if people are going to be reading it often. (It seems to be the best question/answer on this topic). Perhaps you could clean it up some. Links or images may be helpful as well. “Select Case” makes sense to me but isn’t very easy to read, and perhaps would be confusing for people who do not use Basic.

[ajlittoz]

Following @jimk’s advice, I took the liberty to reformat the answer. I hope sehr geehrter Herr @Lupp won’t feel offended (mit respektvollen Tribut).

[ajlittoz]

After saving my edit, I see that items under the first “Case”, numbered as 1. and 2., display with bullets as if I had formatted them with - (dash). Is this a bug or a limitation?

[mariosv]

Done @Jim_K

[Lupp]

@jimk: Thanks for your suggestions.
@mariosv: Thanks for the formatting.
I made some tiny corrections, partly again regarding the suggestions by @jimk .

[mariosv]

Thanks you @Lupp, but I only mark the answer as correct, @ajlittoz was who did it.

[Lupp]

Thanks to all!