If I try to insert a formula with rank on a list of cells selected by CTRL,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 7.0 on ubuntu 18.04
From where do/did you copy the the original formula you want to insert? Maybe the commas are not actual commas but some other unicode or symbol and calc can not translate it in insert.
If you compose the formula using CTRL click to select the cells, libreoffice translate only the first separator (, or 
in the union symbol ~
Could you please describe Step by Step what you are doing.
Like so:
- Select Cell (CTRL + Left Mouse Click)
- … and so on.
otherwise i can not test if i can reproduce the problem.
Yes.
If insert 10 numbers in the column A from cell 1 to cell 10.
In the cell B1 I insert the formula =RANK(A1;(A1;A4;A6;A10);1) where A1;A4;A6;A10 are inserted using Ctrl + Click. When I press enter the formula is translated as =RANK(A1;(A1~A4;A6~A10);1)
Just to release 6.1.3.2 the formula was correctly translated as =RANK(A1;(A1~A4~A6~A10);1)
Ref: tdf#136364.
That’s a bug (I think it’s already in the bug tracker somewhere), use directly the tilde operator instead of relying on the conversion from Excel syntax input:
RANK(A2;(A2:A4~A6:A10~A14:A20~A30:A40))
Which means when selecting cell ranges by mouse, type the tilde character then select the next range without holding Ctrl.
Update 2020-09-11T23:17+02:00:
Bug tdf#136364 will be fixed in LibreOffice 6.4.7 and 7.0.2.
Yes but unfortunately the problem is not solved for me. I have to open an excel file with this type of selection, a file with thousands of lines and hundreds of formulas I can’t find replace all cells to fix the bug. Thank you for your support
You can find all cells with “RANK”, then find \: and replace with ~, checking Current selection only and Regular expressions.
Replacing \: with ~ is a wrong suggestion, it will make things even worse because it destroys the cell range information.
This might be a viable approach, using regular expressions Find&Replace:
- Find:
(~[^,)]+), - Replace:
$1~
This replaces a single instance of such malformed , and has to be repeated for multiple occurrences.
By the fourth comment, may be I misunderstood that
RANK(A2,(A2:A4~A6:A10… should look like
RANK(A2,(A2~A4~A6~A10….
But find (~[^,)]+), did not give me results, because when I paste
=RANK(A2,(A2:A4~A6:A10,A14:A20~A30:A40)), Calc turns it into
=RANK(A2;(A2:A4~A6:A10~A14:A20~A30:A40)). Seem not the case of OP.
Using: LibreOffice 6.3.6.2 (x86); OS: Windows 6.1
Version: 6.3.6.2 (x86)
Build ID: 2196df99b074d8a661f4036fca8fa0cbfa33a497
CPU threads: 4; OS: Windows 6.1; UI render: default; VCL: win;
Locale: es-MX (es_MX); UI-Language: en-US
Calc: threaded
should look like RANK(A2,(A2~A4~A6~A10…
No.
Calc turns it into =RANK(A2;
Your function parameter separator seems to be ; so the find regex would have to be (~[^;)]+; instead.
But with ; separator I doubt that
=RANK(A2,(A2:A4~A6:A10,A14:A20~A30:A40))
is accepted as
=RANK(A2;(A2:A4~A6:A10~A14:A20~A30:A40))
Though
=RANK(A2;(A2:A4~A6:A10;A14:A20~A30:A40))
would be correctly accepted as
=RANK(A2;(A2:A4~A6:A10~A14:A20~A30:A40))
because the bug happens only for three or more range list arguments.
Tested again, and you are right. Thanks.