I open an excel file with a formula like this RANK(A2,(A2:A4,A6:A10,A14:A20,A30:A40)) libreoffice calc translate only 2 commas in tilde and I have RANK(A2,(A2:A4~A6:A10,A14:A20~A30:A40)) and the system get error. Why? Can I resolve the problem setting better libreoffice?
I use libreoffice 6.4 on ubuntu 18.04
Did you try with multiple union: =RANK(A2;A2:A4~A6:A10~A14:A20~A30:A40)?
This works in my system with LO 6.1.6.3 on Windows 10/1903 (64 bit).
The problem may also lie in the source coming from Excel. E.g.: Sometimes Excel files contain a mixture of text and numbers, which is handled differently between Excel and Calc. Excel is more “aggressive” when translating text to numbers, whereas Calc is more “restricted” to avoid ambiguous numbers.
Also for me it works with LO 6.1.3.2, it fails with LO 6.4 (I don’t know the other middle versions)
Seems to be a bug. Only two consecutive references within a () group are concatenated with the “union” operator.