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

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.?

The If function =If(C11<1,0,F10-D11+E11) gives me a blank cell if C11 is empty and 000.00 if it is blank in another spreadsheet WHY
How to simplify Calc formula?
Does ADDRESS function in Calc work within other functions?
No error in nested function when forgot the function separator
Using an OR function/operator in a COUNTIFS?
Find and replace within formula
datedif function in libreoffice with mm/dd/yyyy format
[SOLVED] I keep getting #VALUE! [closed]
Adding numbers with commas and dollar signs in a single cell
Drop down dependent if statement
Calc - hyperlinks that dynamically adjust to sheet re-organisation
Libreoffice Calc basic error:508 when setting formula
combine two senarios If(a1<a2,1) if(a1>a2,2)
Bug in my sumif() regex
How to use MATCH on a variable search range
How to use MATCH on a variable search range
Calc: find function error
VBA formula pasted into cell returns err508
What is causing Err:502 in this formula to forecast the amount of my paycheck on payday?
Using Offset to create a validity dropdown
Using Offset to create a validity dropdown
Using Offset to create a validity dropdown
Simplify formula
[solved] Calc bug when using TEXT to display month in longer formula?
How to deal with a sequence of string in LiberOffice Calc (Index-Match)
How to use cell value as variable in formula TEXTJOIN range?
Why does =TEXT(0,"###") return nothing?
Syntax for nested CONCAT and ROUND functions in one cell
COUNTIFS to get weekdays split in am/pm count
OR inside SUMIF
Excel formula returns #VALUE! in Calc
countif(s) on a named range with function
Can typographic quotes be switched off for ask.libreoffice.org?
Round(Cell Ref,2) but include any redundant 0 e.g. 19.20
Show a value if a text cell includes a specific string
Can sumif take range value as a cell reference
Count if greater than zero in a filtered list (sumtotal, countif)
AGGREGATE Function Errors out in Calc, NOT in Excel
How does this formula work? {=SUM(1/COUNTIF(B2:B400,B2:B400))} Why can it fail to return the correct "countdistinct" result?
Sheet Copy in Calc does not retain formatting/references
Calc: formula: why are tildes auto-replacing commas?
=if()elseif() help?
Match(1,Exact.....) not working with numbers
Lookup matching regular expression in Calc - Addition
SWITCH Function between two ranges instead of individual Values
Check for line feed
Referencing a cell in a named range
Sumproduct #Value Error
DDE link not working 7.4.5
Calc: Conditional/Filtered Median based on various conditions
Is there a way to capitalize multiple words with =Replace in a text?
Guidelines for asking
Using an "Unsubscribe" list
Solved index/max search
How do i get calc to input data on a friday as long as the date is not in the future
what is wrong with this sumifs statement

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.
1 Like

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

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.

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).

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?

Done @Jim_K

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

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

Thanks to all!