Description: Calc currently does not have a function that rounds a value to a determined number of significant digits. Rounding to significant digits is useful for scientific and engineering users as well as for unit tests of functions that return numeric results. Steps to Reproduce: - Actual Results: - Expected Results: - Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:51.0) Gecko/20100101 Firefox/51.0
Specification for function SIGDIGITS: SIGDIGITS Summary: Rounds the value X to the number of significant digits specified by Digits. Syntax: ROUND( Number X ; Integer Digits ) Returns: Number Constraints: 0 < Digits Semantics: round( X * 10^−n) * 10^n with n = floor(log10(x)) + 1 − Digits.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3ee1c41ac72aaae9f364708b1c4df58d7643a341 tdf#105931 Add function ROUNDSIG to Calc. It will be available in 5.4.0. 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.
An unexpected result comes from =ROUNDSIG(T.INV(0.15,2),14) Most of the times, the result is -1.3862065601674 (incorrect). Sometimes it is -1.3862065601673 (correct). Other ROUNDSIG instances with T.INV and 14 digits don't show this behaviour. =ROUNDSIG(T.INV(0.15,2),14) consequently returns -1.38620656016734 (correct). I do not know yet if this is caused by ROUNDSIG() or by something else.
(In reply to Winfried Donkers from comment #3) > =ROUNDSIG(T.INV(0.15,2),14) consequently returns -1.38620656016734 (correct). Oops, this should be =ROUNDSIG(T.INV(0.15,2),15) consequently returns -1.38620656016734 (correct).
(In reply to Winfried Donkers from comment #1) > Specification for function SIGDIGITS: > SIGDIGITS > > Summary: Rounds the value X to the number of significant digits specified by > Digits. > > Syntax: ROUND( Number X ; Integer Digits ) > > Returns: Number > > Constraints: 0 < Digits > > Semantics: > round( X * 10^−n) * 10^n > with n = floor(log10(x)) + 1 − Digits. Should this read " Specification for function ROUNDSIG: Summary: Rounds the value X to the number of significant digits specified by Digits. Syntax: ROUNDSIG( Number X ; Integer Digits ) Returns: Number Constraints: 0 < Digits Semantics: ROUNDSIG( X ; Digits ) = ROUND( X ; Digits - n ) where n = INT( LOG10( ABS( X ))) + 1. " ?
@Winfried Donkers: Sorry. Missed to thank for the good idea and the implementation. (Didn't intend criticism.)
(In reply to Wolfgang Jäger from comment #5) > (In reply to Winfried Donkers from comment #1) > > Specification for function SIGDIGITS: > > SIGDIGITS > > > > Summary: Rounds the value X to the number of significant digits specified by > > Digits. > > > > Syntax: ROUND( Number X ; Integer Digits ) > > > > Returns: Number > > > > Constraints: 0 < Digits > > > > Semantics: > > round( X * 10^−n) * 10^n > > with n = floor(log10(x)) + 1 − Digits. > > Should this read > " > Specification for function ROUNDSIG: > > Summary: Rounds the value X to the number of significant digits specified by > Digits. > > Syntax: ROUNDSIG( Number X ; Integer Digits ) > > Returns: Number > > Constraints: 0 < Digits > > Semantics: > ROUNDSIG( X ; Digits ) = ROUND( X ; Digits - n ) > where n = INT( LOG10( ABS( X ))) + 1. > " > ? Yes, it should. At the time of writing I hadn't chosen a definitive name for the function. (No criticism at all. The function is new (not even in the help text, as I just noticed) and so needs a clear definition.)
There is still missing a specification concerning values of X for which LOG10(X) cannot be calculated. ROUNDSIG should be specified as 0 in this case. In addition (referring to the examples in the first post of bug #115440 ): Since the result is of type Number there should not be a difference between 12.3 and 12.30. The concept of significant digits as traditionally applied in "written arithmetic" requires to regard the so called numbers as texts (sequences of characters, mostly decimal figures). Only by that a trailing zero can bear any information. As there are (traditionally again) no means for whole numbers to distinguish insignificant trailing zeros from significant ones the practice is also bound to the usage of somehow normalized scientific notation. Regarding significance of digits is the main reason for sound applications of the sci formats.
(In reply to Wolfgang Jäger from comment #8) > There is still missing a specification concerning values of X for which > LOG10(X) cannot be calculated. ROUNDSIG should be specified as 0 in this > case. Please explain what you mean. > In addition (referring to the examples in the first post of bug #115440 ): > > Since the result is of type Number there should not be a difference between > 12.3 and 12.30. > > The concept of significant digits as traditionally applied in "written > arithmetic" requires to regard the so called numbers as texts (sequences of > characters, mostly decimal figures). Only by that a trailing zero can bear > any information. As there are (traditionally again) no means for whole > numbers to distinguish insignificant trailing zeros from significant ones > the practice is also bound to the usage of somehow normalized scientific > notation. Regarding significance of digits is the main reason for sound > applications of the sci formats. The examples are not perfect, I will improve them. Also, please add comments for bug #115440 in that bug and not here.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/397d12997a604730ad11316faa34cefd470ee0ff ROUNDSIG() Avoid inaccuracy of pow(10,negative) tdf#138220, tdf#105931 follow It will be available in 7.6.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/a062bceb2509d227cec86845af680a4b69d42df3 Add ROUNDSIG samples to unit test, tdf#138220 tdf#105931 It will be available in 7.6.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-5": https://git.libreoffice.org/core/commit/81458d155a442a98dac0e30ce36725c15901f7a9 ROUNDSIG() Avoid inaccuracy of pow(10,negative) tdf#138220, tdf#105931 follow It will be available in 7.5.4. 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.