I got a document with a data validation expression. The expression uses the LTE function described here:
I tried Libreoffice 7.0.4 (Debian bullseye) and 7.4.4 (Debian bullseye-backports). Both seem to fail to evaluate
=LTE(1;2)
Is the LTE function missing in Libreoffice?
It is not missing, it is luckily simply absent. Having infinite number of duplications (like “LTE is equivalent to the <= operator”) is not a wanted feature.
Maybe you are not aware of, but usually there is a significant difference between a function and an operator. There are only a few languages, which do not distinguish them and I am wondering, whether Excel is one of them. BTW: my wife is not so lucky, because she can not finish her work time recording. But I am fine, if you fell lucky.
Unfortunately, nowhere in your comment had you explained how the difference between a function and an operator translates to being unable to finish a work time recording, other than not knowing how to create a proper formula (and then, there’s a usual XY problem here).
Did somebody yet post the solution regarding the example?
I would think that’s:
Replace the formula =LTE(1;2) by =(1<=2) .
For related cases it may be helpful to know that LT, GT sometimes are used to abbreviate “LessThan”, “GreaterThan” respectively, and that an appended E will then mean “orEqual”.
In addition:
Would LibO Calc implement every nonsense from the GoogleSheets / Excel world, it would lose its compatibility with own older versions.
There are very good reasons, however, to maintain a good Office software working offline and not needing to be updated every other day to the newest fresh version.
Google and MS are commercial. They don’t need to create good software, but software promising good earnings. There obviously are ways to reach the goal even offering unpaid services for a while (baiting). One tiny element of their “struggle for power” may be a successful fight against OpenSource software. Wilfully creating compatibility problems again and again may be a part of the strategy.
To developers:
Might automatic conversion-on-loading of such unneeded functions using operators be a means?
Indeed, if the functions are stored in the file using some unambiguous names (e.g., LibreOffice uses some lengthy unambiguous names for non-standard extensions: say, WEEKSINYEAR is stored in ODS as ORG.OPENOFFICE.WEEKSINYEAR).
If Google chose to store the non-standard functions “as is”, it would be impossible, creating confusion like “is it a column name? is it a named range or database range name?” and so on.
I have tried to edit a XLSX file!
And when I open the same file in Excel, it works as expected: no error because of broken data validation. When I open a XLSX file in LibreOffice Calc and I get a data validation error, although I do not get the same error in Excel, than this is nothing else but a bug in LibreOffice Calc. I was already on the bug report page of LibreOffice, but there I was told to talk about this here. But nothing I have read till now has changed my mind.
Till now I think the question can be answered with: yes it is missing.
Any other ideas?
No it isn’t!
, because …
Please provide a test file to check.
Please provide a reference to the bug report.
…if I would use Google-sheets ( I don’t ) I would use the more intuitive and cross-compatible: =a<=b
I have created a sample GSheet with LTE in a cell formula, and in a validation rule, and downloaded it as XLSX:
LTE.xlsx (4.7 KB)
I have tested it in MS Excel 2016, and indeed, it didn’t give validation errors. It simply ignored the function that it doesn’t know. Yes, surprise: it doesn’t know it. I also tested in in Excel Online, and the result was the same.
OTOH, LibreOffice shows an error - it rejects any input. And you know what: IMO, this is much better than to silently ignore the problem.
Calc works better than Excel in this regard.
Do not use the incompatible functions if you want to work efficiently.
Note: The LibreOffice (and the Apache OpenOffice) are not MS Office clones. And the GSheet is not an Excel clone and it is not a Calc clone.
There is not (never was and never will be) 100% compatibility between the different file types and between the different applications.
You can create it as a Macro function:
Function LTE(FirstVal as double, SecondVal as Double) as boolean
LTE = (FirstVal <= SecondVal)
End function
Store the function into the MyMacros - Standard - Module1. Then your .XLSX file will work, when you open it in the LO.
The function is absent, but obviously not missed here. You may file a request for enhancement at the place you already know: bugzilla for LibreOffice.
.
A place where you definitly should give a bug-report is MS for Excel, for not giving an error.
How can Excel ignore the missing function? Does it show a function result that is stored in the file? Does it show an empty string?
The “ignores” applies to the “validation”; I also provided an access to the online version for testing. Indeed, when the function is in cell formula, the cell result is #NAME?.
Here is a comparison table for the available functions of the spreadsheet applications LO Calc and Excel:
Table
.
And here is the function list of the GShets:
GSheets
.
List of the Excel cell functions:
Excel
.
List of the Calc cell functions of the Apache OpenOffice:
OpenOffice
.
Operator list of the LibreOffice Calc:
LO operators
To ignore (not report) an error actually isn’t a bug in exclusive ownership of MS. We have to confess that Calc also does it - in this case and in cases of formulas used in conditions for ConditionalFormatting. The result missing due to an error is handled as if FALSE, as far as I can see.
Many contributors already advised here to write and test formulas for CF and the like first in cells.
I have not verified it, but if it is the case, that Excel silently ignores a validation in case of a validation failure (not a successful false), then this is exactly what a user usually wants.
A validation is not necessary, it is just a safety-feature. It is a guidance for people, who can not enter valid data without help. People, who can enter valid data, do not need this guidance.
When a validation failure is treated in the same way as a successful validation resulting in the value false, then neither the boneheads, who need guidance, nor the smart people, who do not need guidance, can use the document.
To make it a bit more bold: an example from the real world.
A Excel-guy and a Loc-guy have both a house in a street, which needs a new sewerage. A civil engineering company makes a huge hole right in front of both houses, but misses to install a fence, which prevents that people die by falling accidentally in the hole. Both guys are out of food. What will they do?
The Excel-guy says: shit on the safety and get some food.
The Log-guy says: I can not pass the hole without the safety of the fence. I will happily die, because life without safety does not make any sense.