Bug 105657 - Option 'Precision as shown' working absurdly with formats from the 'Fraction' category..
Summary: Option 'Precision as shown' working absurdly with formats from the 'Fraction'...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: low enhancement
Assignee: Laurent BP
QA Contact:
URL:
Whiteboard: target:5.4.0 target:5.3.3
Keywords:
Depends on:
Blocks:
 
Reported: 2017-01-31 22:57 UTC by Wolfgang Jäger
Modified: 2017-04-10 13:53 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 Wolfgang Jäger 2017-01-31 22:57:47 UTC
(Ex ante: The option 'Precision as shown' is a dangerous tool to do things that should be done thoroughly using explicit rounding sloppily when in a lazy mood . The option should never have been introduced. I strongly discourage everybody to use it. However, it exists, and thus it should work as reliably and sensibly as possible. Unfortunately the option was also entitled to work on cells whose numeric values are formatted the 'Fraction' way. This may be considered insane from the beginning. It's a fact nonetheless.)

The precision a fraction can achieve is limited by the maximum denominator allowed. 
The worst case of the length s of a range of values within the interval from 0 through 1 that cannot be distinguished by fractions with a maximum denominator of M allowed should be: s = 1/(2*(M - 1)) .

For the often used 
M = 9  this is 1/16
M = 99         1/196
M = 999        1/1996 ...

To get things more familiar - and keeping the secure side - we may consider an inaccuracy of roughly 10^(-D) if D is the number of the nines in 9, 99, 999 ..., or, turned the other way, if the maximum denominator is (10^D - 1).

OK. Let's count the question marks in the formal denominator of the format code  and we have a "precision" applicable if 'Precision as shown' is selected. 

What Calc seemingly does in fact is to count the number N of positions in the formal numerator and to set the "precison" for the cell to 10^(-N). 

The 'Precision as shown' actually is applied to the intermediary result in advance of setting theCell.Value (and of formatting this for theCell.String). 

Thus we may enter the formula =1/3 in a cell formatted with the code # ?/??? and get shown 2/7 if 'Precision as shown' is ON. 

This behaviour is inherited from OOo. It should be changed nonetheless.
Comment 1 Aron Budea 2017-02-13 11:53:23 UTC
Laurent, would you mind giving this a thought?
Comment 2 Buovjaga 2017-02-13 12:09:11 UTC
Eike thought this is a low priority enhancement, so setting as such.
Comment 3 Laurent BP 2017-02-13 13:42:26 UTC
Hi,

I agree with Eike, it is low priority bug: 
- fraction format is not widely used (I found many bugs with this format that were not declared neither on LibO, AOO or OOo)
- "precision as shown" should only be used by aware user, and it is neither widely used
- fraction format + "Precision as shown" is buggy from the origin

I noticed that new algorithm given by Wolfgang and introduced in LibO 5.3, change nothing to this behavior.

But I think it may be an interesting challenge to look deeper in it.
Comment 4 Laurent BP 2017-02-13 15:59:01 UTC
Bug is more vicious than describe by Wolfgang because result depends on numerator length.
1. Set "Precision as shown" option (Tools > Options > LibreOffice Calc > Calculate)
2. Enter formula =1/3
3. Set number format to General
Display is 0.333333 (length depends on column width)
5. Select Fraction format # ?/?
Display is 2/7
4. Change cell format through dialog (Format > Cells > Numbers)
5. Select Fraction format # ??/?
Display is 1/3

If numerator length is larger than denominator length, there is no bug. To avoid extra blank, replace numerator with many #
# ####/? or # ####/?? or # ####/???
Comment 5 Laurent BP 2017-02-13 22:19:29 UTC
(In reply to Laurent BP from comment #4)
> If numerator length is larger than denominator length, there is no bug. To
> avoid extra blank, replace numerator with many #
> # ####/? or # ####/?? or # ####/???
Actually, Precision is taken from NumFor[0].nCntPost
http://opengrok.libreoffice.org/xref/core/sc/source/core/data/documen4.cxx#RoundValueAsShown
 which is not correct for fraction. It should be nCntExp like here:
http://opengrok.libreoffice.org/xref/core/svl/source/numbers/zformat.cxx#GetNumForInfo

However, I noticed another bug for "Precision as shown": it takes precision from the first sub-format, which may be wrong with such (strange) format:
0.00;-0.0
Comment 6 Wolfgang Jäger 2017-02-14 00:16:46 UTC
(In reply to Laurent BP from comment #4)
> Bug is more vicious than describe by Wolfgang because result depends on
> numerator length.

Not quite. From my original post: 
"What Calc seemingly does in fact is to count the number N of positions in the formal NUMERATOR and to set the "precison" for the cell to 10^(-N)."

You also find detailed info there about the actual accuracy depending on the number of places in the denominator.


Now I have a question: What are the cell properties 
.FormulaResult and .FormulaString for?

Concerning Comment #5: Yes, it's a mess from the beginning.
Comment 7 Wolfgang Jäger 2017-02-14 00:35:25 UTC
Cells with Time formats seem to simply ignore the 'Precision as shown'.
Comment 8 Commit Notification 2017-02-20 18:36:30 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5706b29974c1c3ab0ba5a23685accf2fbebc3e06

tdf#105657 Treat "Precision as shown" for fractions

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 9 Commit Notification 2017-02-20 19:31:06 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=820cb1f86f3fd177877b8719315fc03534e2049c

don't obtain the underlying SvNumberformat thrice, tdf#105657 follow-up

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 10 Commit Notification 2017-02-21 21:06:28 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=52b464f31a162c2e90cb5482ffa2a7ff8dd386d5

Remove unused function. Following tdf#105657

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 11 Commit Notification 2017-03-02 17:54:11 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7496f7d3cae8a932dc43ede8a30a99289366a264

Tests for "Precision as shown" following tdf#105657 tdf#106052

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 12 Commit Notification 2017-04-10 13:49:06 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9f70f8761a8cc180171022ffcffc0094c4957057&h=libreoffice-5-3

tdf#105657 Treat "Precision as shown" for fractions

It will be available in 5.3.3.

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 13 Commit Notification 2017-04-10 13:49:41 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2864c32733df6b0e9975ed5ee03a747cd2932472&h=libreoffice-5-3

don't obtain the underlying SvNumberformat thrice, tdf#105657 follow-up

It will be available in 5.3.3.

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 14 Commit Notification 2017-04-10 13:53:48 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e9d13f703f212b317682c35877571903da18b4af&h=libreoffice-5-3

Tests for "Precision as shown" following tdf#105657 tdf#106052

It will be available in 5.3.3.

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.