Calc gives: convert_add(1;"at";"Pa") = 101325 convert_add(1;"atm";"Pa") = 101325 but correct is: convert_add(1;"at";"Pa") = 98066,5 technical atmosphere convert_add(1;"atm";"Pa") = 101325 normal atmosphere
On pc Debian x86-64 with master sources updated today, I can reproduce this.
It must be there: http://opengrok.libreoffice.org/xref/core/scaddins/source/analysis/analysishelper.cxx#2549 I take it.
Julien Nabet committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c2dd0ef4b891a3d126dbe70ab5b76016b69d491c Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
For 4.2 and 4.1, commits have been submitted to gerrit.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=054b74222d72a61db3c67b32d62f99a1a0a5e9d6&h=libreoffice-4-2 Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer It will be available in LibreOffice 4.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4139dc70da04fe3d6a86bbab1e7bf6f11d812f04&h=libreoffice-4-1 Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer It will be available in LibreOffice 4.1.5. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Hello all, Sorry that I come late, but looking at OASIS specification[1], both the "atm" and "at" units are the same. I didn't say that Konrad was wrong, but I think we should revert this for the sake of compatibility to the specs. Another choice is, IMHO, proposing an amendment to the specs. Thanks :) [1] http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018496_715980110
Korrawit: I understand the point but I don't know at all the process to amend Oasis specs. Any idea who may help here?
(In reply to comment #8) > Korrawit: I understand the point but I don't know at all the process to > amend Oasis specs. Any idea who may help here? From what I remembered, Miklos Vajna and Michael Meeks are involved in work at ODF-Next at OASIS.
Miklos/Michael: any idea about the process to amend Oasis specs? Does it worth it or should we revert the patch?
I added Kohei Yoshida because he is an expert from spreadsheet.
IIRC Michael Stahl is a good guy for tweaks to ODF. We keep our implementor notes here - prolly worth adding something to that so it is tracked: https://wiki.documentfoundation.org/Development/ODF_Implementer_Notes if it is not there already.
Argh.. sigh.. the OpenFormula CONVERT function was specified after Excel, plus some extensions, and also Excel treats "atm" as an equivalent of "at", latest checked with Excel 2013. We should not come up with different factors on our own in this case. I'll revert the corresponding commits.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=63153f10e0a7d16511878bbfcd97e2c8781a5564 Revert "Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer" The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=5491192f98a9b50e1b8d2ecd9d711ee373246d15&h=libreoffice-4-2 Revert "Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer" It will be available in LibreOffice 4.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=152ee33b6c70315e1103b12de745b3f9c675c0b2&h=libreoffice-4-1 Revert "Resolves: fdo#72174 convert_add from "at" or "atm" to "Pa" gives same answer" It will be available in LibreOffice 4.1.5. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Users involved with scientific matters might assume that LO and ODF "Standards" would correctly follow scientific standards too. Such assumption might lead to wrong conclusions from users, for example when analyzing data obtained from Calc. Perhaps some note(s) in LibreOffice's documentation / help / wiki / Calc guide(s) should be added regarding this issue (for example for the help regarding the involved Calc functions)? Regards, Ady.
I "unassign" myself since I can't do more and am quite disappointed about this. IMHO, ODF and even less Excel do not define standards about measures (it's not their role). Of course odf files must follow odf standards but in this case, we know that the standard includes a mistake. Therefore we should follow real standards + ask to amend ODF standard or disable this conversion but not validate an error. Now, it's just my humble opinion.
Grief - wheat a vexed problem ! In general, doing what people expect - ie. the same as Excel is really a good idea. Of course, when that is wrong it's hard to know what to do. Thanks for unwinding this Julien - and raising the profile of it. Of course, we could add some libreoffice specific function that does what we think is right, and then a very large amount of filter logic to convert to/from Excel/old-ODF ? - but that seems like a lot of work (?)
Just for information, I opened a thread here to know if it's possible to amend OpenDocument format: https://lists.oasis-open.org/archives/office-comment/201312/threads.html
I've read the comment on the mentioned ODF office-comment list. I think Julien is right, that the units "atm" and "at" are different and that they need two separate rows in the spec. Gnumeric circumvents the problem. It does not allow unit "at". Perhaps LibreOffice should do the same for ods format. That way the user notices that the result had been false, and newer documents will not be based on a wrong "at" conversion. Then later on, when the ODF specification is corrected, LibreOffice can us the correct conversion with less conflicts.
Apparently the ODF TC has decided to fix this for ODF 1.3, and also "ODF 1.2 Errata 01"??? So we will have to implement both behaviours, one for importing xls/xlsx files and ODF 1.2 and before, and one for ODF 1.2 errata 01 and later? I also wonder about users being very confused why the behaviour is different in different ods files... depending on the version of ODF they are tagged with, which is AFAIK not clearly displayed in the UI. Frankly I'm a bit surprised one would change that in an errata, especially since it seems it was done very much on purpose. Are errata even considered different versions, meaning they get a different version tag in the files?
There is no way to check the "errata level" of an ODF document because errata are not allowed to make substantive changes. i don't know anything about this, most likely didn't attend the meeting where it was discussed, so no idea if there is actually the intent to fix this in ODF 1.2 errata; the "Resolution" says "Will fix in ODF 1.3" but the "Fix for" also lists 1.2 errata...
Just in case someone comes across this again (I was asked by an OASIS-TC member about my opinion), for clarification: http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#CONVERT *already* deprecates the use of "at" as an equivalent of "atm", listed as "atm" ("at") Nevertheless, the misleading use of unit is in the wild since ages. The ODFF specification states at the bottom of the unit table: "The unit symbols in parentheses are deprecated unit symbols; evaluators shall support these unit symbols." The correct way is to specify a new unit symbol to calculate the "at-correct" conversion.
(In reply to Eike Rathke from comment #24) >... > The correct way is to specify a new unit symbol to calculate the > "at-correct" conversion. I'm not sure to understand. Would it be an LO-internal new unit symbol? If yes, how LO would distinguish in a file the use of "real at" and the "wrong at"? At least, I hope they don't mean a new "at-correct"/"at-fixed"/... symbol available for users since it would be a bit cumbersome.
No, I meant an additional unit symbol to be specified by ODFF. Sorry for confusion. And yes, that would mean an "at-correct" symbol because "at" is spoiled forever and either calculated as Excel does and specified, or not calculated at all as Gnumeric does.
update: this was resolved now at OASIS, quoting from https://issues.oasis-open.org/browse/OFFICE-3851 Resolution: "at" Deprecated. Note: Interpreted inconsistently by existing implementations. "atm" Standard atmosphere = 1.0132510+5 Pa "SI_at" Technical atmosphere = 9.8066510+4 Pa