Created attachment 135627 [details] Example test case Nested ISNUMBER(), LEN(), TRUNC() functions are evaluated improperly. According to function wizard, it's possible to get: * Err:502 or Err:504, * #VALUE!, * wrong type (e.g. ISNUMBER(12.16) = 5), * wrong value (e.g. TRUNC(12.16) = 5). Functions can mistakenly return the result of another functions. Breaking apart such a formula is the only solution.
I saved the file as .xlsx and opened in excel. The result from excel is B3= False B4 = false C4 = false -> no error. Setting as new. In LO Version: 6.0.0.0.alpha0+ are results B3, B4, C4 = Error.
I just want to clarify some things because I think I wasn't completely accurate above. Errors are only got on systems where decimal mark isn't comma, because TEXT() is unable to format the value to the specified, locale dependent text. My real issue is the false return values showed by Function Wizard. The same formula there is in cell B3 and in cells B4 and C4 (with the only difference it has been split into two smaller parts in the latter case), but Function Wizard shows two different return values for the same function: [B3] ISNUMBER = 7 [B4] ISNUMBER = TRUE()
I am from Spain and I have set Languaje setting of LO to: User interface: English (USA) Locale setting: English (USA) Decimal separator key: checked Same as locale setting (.) So decimal separator is "." and argument separator of functions is "," Seems as in funcion TEXT of calc, the decimal separator of the format string is ever "." and the thousands separator is ever "," (is excel seems localized). So TEXT(12.1126,"#,##########") returns string "12" Then RIGHT(TEXT("12.1126","#,##########"),LEN(TEXT("12.1126","#,##########"))-LEN(TRUNC("12.1126"))-1) Equals to RIGHT("12",2-2-1) that returns "Err:502" I think the issue is not with function processing but with the function wizard that shows incorrect values when the function (once results of type error appear) becomes more complex.
I make a little mistake: I said "Seems as in funcion TEXT of calc, the decimal separator of the format string is ever "." and the thousands separator is ever "," (is excel seems localized)." but really the format code of function TEXT is localized. But this is not relevant to the example file. Only is important to reproduce issue in LO installations with diferent languages. As the language of cell is set to default if locale is set to "English (USA)" the decimal separator to use in the format code is "." and the thousand separator to use in format code is "," so rest of comment is fine. I checked step by step the function in cell C4 and the correct result when cell B1 is equal to 12.1126 is Err:502 as show in cell. The issue is only related to function wizard.
Using TEXT(12.1126;"#,##########") in a locale that does not use comma as decimal separator is doomed to fail. Replacing all #,# with #.# makes the formulas work. Not a bug.