Bug 105931 - Add function to Calc that rounds to significant digits
Summary: Add function to Calc that rounds to significant digits
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.0.0.alpha0+
Hardware: All All
: medium enhancement
Assignee: Winfried Donkers
URL:
Whiteboard: target:5.4.0
Keywords:
Depends on:
Blocks:
 
Reported: 2017-02-11 10:22 UTC by Winfried Donkers
Modified: 2018-02-05 06:57 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Winfried Donkers 2017-02-11 10:22:33 UTC
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
Comment 1 Winfried Donkers 2017-02-11 10:40:02 UTC
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.
Comment 2 Commit Notification 2017-02-20 17:05:13 UTC
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.
Comment 3 Winfried Donkers 2017-02-28 14:43:16 UTC
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.
Comment 4 Winfried Donkers 2017-03-04 08:55:03 UTC
(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).
Comment 5 Wolfgang Jäger 2018-02-02 15:03:51 UTC
(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.
"
?
Comment 6 Wolfgang Jäger 2018-02-03 23:27:54 UTC
@Winfried Donkers: Sorry. Missed to thank for the good idea and the implementation.
(Didn't intend criticism.)
Comment 7 Winfried Donkers 2018-02-04 13:56:24 UTC
(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.)
Comment 8 Wolfgang Jäger 2018-02-04 14:53:24 UTC
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.
Comment 9 Winfried Donkers 2018-02-05 06:57:56 UTC
(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.