https://bugs.documentfoundation.org/show_bug.cgi?id=172753
Bug ID: 172753
Summary: Mess in file format of binomial distribution functions
Product: LibreOffice
Version: 26.8.0.0 alpha0+ master
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Create a spreadsheet with these formulas
=B(20;2/5;3;7)
=BINOM.DIST(7;20;2/5;TRUE())
=BINOMDIST(7;20;2/5;TRUE())
Save it to ods and inspect the file markup. You see
table:formula="of:=BINOM.DIST.RANGE(20;2/5;3;7)"
table:formula="of:=COM.MICROSOFT.BINOM.DIST(7;20;2/5;TRUE())"
table:formula="of:=BINOMDIST(7;20;2/5;TRUE())"
The ODF format has only two functions
6.18.9 BINOM.DIST.RANGE
6.18.10 BINOMDIST
The function BINOM.DIST.RANGE from ODF is the function B in UI of LibreOffice.
So that is correctly written to file.
The function BINOMDIST from ODF is the function BINOM.DIST (op-code
ocBinomDist_MS) and the function BINOMDIST (ocBinomDist) in LibreOffice. These
two functions are both calculated by ScBinomDist(). A distinction between these
two function is not needed in ODF format.
Expeted behavior of LO: Both functions BINOM.DIST and BINOMDIST are written as
BINOMDIST to the ods file.
Open the saved ods-file in Excel. The formula
COM.MICROSOFT.BINOM.DIST(7;20;2/5;TRUE()) is unknown to Excel. Thus using
BINOMDIST in both cases would increase interoperability.
Now create again a spreadsheet with the three functions and save it to "Excel
2010-365 Spreadsheet (*.xlsx)". Inspect the file markup. You see
<f aca="false">B(20,2/5,3,7)</f>
<f aca="false">_xlfn.BINOM.DIST(7,20,2/5,TRUE())</f>
<f aca="false">BINOMDIST(7,20,2/5,TRUE())</f>
Open the saved files in Excel.
Problem:
<f aca="false">B(20,2/5,3,7)</f> is unknown to Excel.
Excel has this function as BINOM.DIST.RANGE in its UI and writes it as
<f>_xlfn.BINOM.DIST.RANGE(20,0.4,3,7)</f> to xlsx file. The function
BINOM.DIST.RANGE is available since 2013. A similar function does not exist in
older Excel versions.
Expected behavior of LO: The function B is written as _xlfn.BINOM.DIST.RANGE to
xlsx format.
Excel has in its UI the functions
BINOM.DIST.RANGE file markup _xlfn.BINOM.DIST.RANGE
BINOM.DIST file markup _xlfn.BINOM.DIST
and for compatibility reasons the function
BINOMDIST file markup BINOMDIST
The function BINOM.DIST is available in Excel since 2010. Thus when saving to
"Excel 2010-365 Spreadsheet", LibreOffice should write _xlfn.BINOM.DIST for
both BINOM.DIST and BINOMDIST. In case of saving to "Excel 2007 (*.xlsx)" both
function should be written as BINOMDIST.
--
You are receiving this mail because:
You are the assignee for the bug.
