(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.
Laurent, would you mind giving this a thought?
Eike thought this is a low priority enhancement, so setting as such.
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.
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 # ####/???
(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
(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.
Cells with Time formats seem to simply ignore the 'Precision as shown'.
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.
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.
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.
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.
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.
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.
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.