Dear All,
after upgrading libreoffice, some formulas stoped working, presenting Err:509.
The formula is:
=IF(ISNUMBER(SMALL((C82~F82;I82~L82);COUNTIF((C82~F82;I82~L82);0)+1));SMALL((C82~F82;I82~L82);COUNTIF((C82~F82;I82~L82);0)+1);0)
this formula selects the lowest value greater than zero and lights the cel which contains the value.
Before the upgrade, it was working.
Can anyone help me out to find the error?
Thx in advance.
I need the file…
The tilde obviously is signaling a syntactical error and may have replaced a colon here and there, but without knowing the exact situation I cannot reliably help.
Also: such a formula cannot “light a cell”. The highglighting must have been an effect of conditional formatting which in turn used the correct formula result.
ByTheWay: You shouldn’t expect to be able to use spreadsheets in a sensible way if you don’t know anything about their working. You obviously didn’t design the formula yourself. Otherwise you would also know how to fix it. First addressee for the question should be the one who wrote the formula.
The weird thing is: If I copy the formula above into an empty spreadsheet (using ; as seaparator in function), I get:
=IF(ISNUMBER(SMALL((C82~F82~I82~L82);COUNTIF((C82~F82~I82~L82);0)+1));SMALL((C82~F82~I82~L82);COUNTIF((C82~F82~I82~L82);0)+1);0)
i.e. all C82~F82;I82~L82 turned to C82~F82~I82~L82 with absolutely no further action and there is no error message.
Tested using LibreOffice:
Version: 6.4.5.2, Build ID: a726b36747cf2001e06b58ad5db1aa3a9a1872d6
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5;
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threadedTja - alien format .xls. Save as .ods and the problem is gone (just tested and you even don’t need to change anything - see comment above) and you may want to file a bug report for the .xls export filter.
Opening the file in Excel render C82~F82~I82~L82 as C82;F82;I82;L82.
Btw, already this SMALL((C82~F82;I82~L82);... as the original expression is a bit odd, what is (C82~F82;I82~L82) actually expected to be there? And yes, that is the same as (C82~F82~I82~L82) (or (C82;F82;I82;L82) or (C82,F82,I82,L82) in Excel world), so what was the original intention?
@lupp
you shouldn’t expect to be able to use spreadsheets in a sensible way if you don’t know anything about their working. You obviously didn’t design the formula yourself. Otherwise you would also know how to fix it. First addressee for the question should be the one who wrote the formula.
yes, i wrote it some years ago, with help from some people.
Libreoffice Version: 6.4.4.2 -
Before, this formula was working.
After upgrade, the behavior is: open the sheet, error:509 shows up. I enter the cell, copy the content (formula), paste it to the same cell, and it works.
After quitting and opening again, same error shows up.
Really don’t know what is going on.
anyone saw this behavior?
This is not an answer to the original question and answers of this site should provide a solution to a question. Any other communaction should go via add a comment (or in case of adding additional details to a question by edit of the question.
ok, got it
The accursed formulas look rather strange to me anyway: The surrounding parentheses, the doubled calculations?
I personally also cannot remember to ever have used unions of broken ranges the way you seemingly do. Not knowing what you actually wanted to achieve I couldn’t check for the semantics.
There may have been some luck, however, and the formulas as I “fixed” them in the attachment do what you wanted. Have a look:
ask255961.ods
did not work. i tried under openoffice, and it’s working 
Quoting @pw44: “did not work.”
What should this tell me? This is not how peer-to-peer help works.
I suppose you can imagine that I spent some time trying to help you. Might you be able now to afford the time needed to tell me in what way my suggestions did not work? (They were applied to the cells B63, B82, B83 of the ‘Tabelle1’.)
Exact LibO/AOO version(s), afflicted cells, error message or bad result? What error message or result as compared to your expectation (if any) per affliccted cell?
BTW: There are very strange errors sometimes caused by user profile corruption.
See LibreOffice user profile - The Document Foundation Wiki.
i tried the suggestion, and did not work. i also informed version (Libreoffice Version: 6.4.4.2 -) and sent the atual spreadsheet (C:\fakepath\teste.xls). sure i know you spent time to help me, and i thank you very much for that. what i reported is that on libreoffice it didn’t work.
It (the updated formulas) worked for me in more than one LibO version, including 6.4.4.2, basically. However, I couldn’t judge if the results were what you expected, because you didn’t tell.
For me it showed B63 = R$1,000.00, B82 = R$67.20, B83=$11.43.
Why don’t you simply report what the 3 cells showed for you?
Did you check for the probable user profile corruption?
Well, anyway. Over.
You could at least had told in which cell the error occurs first (most Err:509 cells are just because they reference some other cell with that error) and that contains the formula you mentioned. Ok, it’s B63.
Yes, that looks like an error in the binary .xls format or the filter writing or reading it.