Description: In MS Excel the formulae DEC2BIN, DEC2OCT, and DEC2HEX have valid inputs as integers m in the range -2^n <= m < 2^n, where n is given by DEC2BIN: n=9 DEC2OCT: n=29 DEC2HEX: n=39 LibreOffice Calc gets this right for DEC2BIN and DEC2OCT, but not DEC2HEX. There, the valid range is erroneously: -2^39 - 1 <= m < 2^39 + 1. In other words, in Excel we have: =DEC2HEX(2^39) == #NUM! =DEC2HEX(-2^39 - 1) == #NUM! But in LOCalc: =DEC2HEX(2^39) == "8000000000" =DEC2HEX(-2^39 - 1) == "7FFFFFFFFF" This is surprising and does not seem like intended behaviour. I have not yet investigated whether the other base conversion functions have inconsistent bounds as well. Steps to Reproduce: In a cell do =DEC2HEX(2^39) or DEC2HEX(-2^39 - 1) Actual Results: Answers are "8000000000" and "7FFFFFFFFF" respectively. Expected Results: Answers should be #NUM! in both cases, in accordance with Excel. Reproducible: Always User Profile Reset: No Additional Info: OS: Manjaro Linux LibreOffice Calc version: 6.4.7.2
Why LO should be in accordance with Excel or more precisally, with the same limitations? Unless you consider functions DEC2BIN, DEC2OCT, DEC2HEX are MS functions? (but I don't see doc about this).
I can reproduce the reported inconsistency with 7.1.0 Beta1, i.e.: DEC2BIN(2^9) and DEC2BIN(-(2^9)-1) gives Err:502 DEC2HEX(2^39) gives "8000000000" and DEC2HEX(-(2^39)-1) gives "7FFFFFFFFF", not errors Version: 7.1.0.0.beta1 (x64) Build ID: 828a45a14a0b954e0e539f5a9a10ca31c81d8f53 CPU threads: 2; OS: Windows 10.0 Build 18363; UI render: default; VCL: win Locale: zh-CN (zh_CN); UI: zh-CN Calc: threaded Also both DEC2HEX(2^39) and DEC2HEX(-(2^39)) gives "8000000000", so this is definitely a bug. (In reply to Julien Nabet from comment #1) > Unless you consider functions DEC2BIN, DEC2OCT, DEC2HEX are MS > functions? (but I don't see doc about this). MS has support page for DEC2HEX: https://support.microsoft.com/en-us/office/dec2hex-function-6344ee8b-b6b5-4c6a-a672-f64666704619 I believe this is Eike's area of expertise, adding him to CC.
(In reply to Ming Hua from comment #2) > ... > (In reply to Julien Nabet from comment #1) > > Unless you consider functions DEC2BIN, DEC2OCT, DEC2HEX are MS > > functions? (but I don't see doc about this). > MS has support page for DEC2HEX: > https://support.microsoft.com/en-us/office/dec2hex-function-6344ee8b-b6b5- > 4c6a-a672-f64666704619 > ... I don't understand, is "DEC2HEX" a kind of "proprietary" function of MS? Why couldn't LO use these function names and expand what Excel can do? Is LO bound to mimic Excel even in its limitations?
(In reply to Julien Nabet from comment #3) > (In reply to Ming Hua from comment #2) > > ... > > (In reply to Julien Nabet from comment #1) > > > Unless you consider functions DEC2BIN, DEC2OCT, DEC2HEX are MS > > > functions? (but I don't see doc about this). > > MS has support page for DEC2HEX: > > https://support.microsoft.com/en-us/office/dec2hex-function-6344ee8b-b6b5- > > 4c6a-a672-f64666704619 > > ... > I don't understand, is "DEC2HEX" a kind of "proprietary" function of MS? > Why couldn't LO use these function names and expand what Excel can do? Is LO > bound to mimic Excel even in its limitations? Well, LO Calc is mimicking Excel's limitations already. The bounds of the formulae seem to be so that the outputs are constrained to be 10 characters long, but that's just an arbitrary thing Microsoft chose. LibreOffice doesn't have to abide by that -- it could choose to allow much bigger inputs, yet it doesn't, for what I assume are compatibility reasons. So if compatibility and consistency are goals, the bounds should match Excel's.
(In reply to cameron.pinnegar from comment #4) >... > Well, LO Calc is mimicking Excel's limitations already. The bounds of the > formulae seem to be so that the outputs are constrained to be 10 characters > long, but that's just an arbitrary thing Microsoft chose. LibreOffice > doesn't have to abide by that -- it could choose to allow much bigger > inputs, yet it doesn't, for what I assume are compatibility reasons. So if > compatibility and consistency are goals, the bounds should match Excel's. So you prefer LO sticks to Excel limitations instead of LO being able to do better just for compatibility sake? It seems most people prefer Excel compatibility as a top priority for Calc, I find it crazy. Anyway, don't worry, I must be the only one (at least never saw another one) to think like this, so I suppose this "bug" will be "fixed" :-) => uncc myself
personally I prefer Julien's opinion. but putting aside that, I can provide a souce code pointer. https://opengrok.libreoffice.org/xref/core/scaddins/source/analysis/analysishelper.cxx?r=93c64a61&fi=ConvertFromDec#704 This is not fNum <= fMin || fNum >= fMax
oops,typo souce => source
https://opengrok.libreoffice.org/xref/core/scaddins/source/analysis/analysis.cxx?r=107399d6#662 more note: SCA_MAX2 and SCA_MAX8 are odd numbers, but SCA_MAX16 is an even number.
All three functions are Excel functions. It is futile to discuss whether Calc could do better than Excel, the definition of DEC2HEX is there https://docs.oasis-open.org/office/OpenDocument/v1.3/cs02/part4-formula/OpenDocument-v1.3-cs02-part4-formula.html#DEC2HEX and the current implementation has one-off errors in its interval.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/70ea6b36df9ede18b135876d9b9da9945f6c129b Resolves: tdf#139173 One-off error in limits for DEC2HEX() It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/c/core/+/108896 for 7-1 https://gerrit.libreoffice.org/c/core/+/108897 for 7-0
Verified in Version: 7.2.0.0.alpha0+ Build ID: 6bb6eb1692d7dd432103d0e7278534390084caf6 CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded @Eike, thanks for fixing this issue!!
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-1": https://git.libreoffice.org/core/commit/072b43a4bdf8392c9a6a8cb1ad5e8a3e61c60117 Resolves: tdf#139173 One-off error in limits for DEC2HEX() It will be available in 7.1.0.2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://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-7-0": https://git.libreoffice.org/core/commit/8c7266b084fe95460c1e8b5e27bb4464ca1cc20a Resolves: tdf#139173 One-off error in limits for DEC2HEX() It will be available in 7.0.5. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/ddd2f4361cf0272dbf34ad4dd4a3874ab490b1b3 tdf#139173: sc_addin_functions_test: Add function It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/ef1b6642feb39aab502c90436286aeee9a7fa241 tdf#139173: sc_addin_functions_test: Add dec2bin and dec2oct functions It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.