Hello everyone!
For particular situation of value 0.09 being displayed as a fraction ms excell shows ‘1/11’ while libre office calc shows ‘8/89’ with formatting ‘#?/??’.
Is there any way to ensure file displays always the same fraction value regardless of whether it’s opened in LOC or MSE?
(Reopened by @Lupp hoping to get an answer to the question contained in his comment below.)
It seems that LibreOffice calculate this more precisely. 1/11 is 0,090909 while 8/89 is [edit newbie-02 2021-03-10] think that was a typo 0,8988 0,08988 [/edit]. The difference between actual value (0,9) and the LibreOffice is far less than difference between MS Excel and actual value. I guess this is algorithm which can be change a little bit in Format → Cells. There are two types of Fraction format which result in different fraction result. Try to play with this to see if it is possible to get similar result.
Thank you for your reply.
I’ve tried multiple different formatting patterns for fraction and failed to get the same result as ms excell. The only one that would be interpreted the same is ‘#?/???’ but that’s an exact value and I need an approximation with two digits in denominator.
I also need a confidence that all values are always displayed in the same way in both programs.
(Sorry. I missed this question when it was “fresh”. Otherwise I would have commented earlier.)
Why should somebody want to get the MS-Excel result despite the fact that it’s absolute error is more than 8 times as large as the error produced by the LibO Calc result?
That’s like booking a flight over the Atlantic and preferring the aicraft known for the higher rate of crashes.
As long as MS-Excel ignores the applicable mathematics Calc must not follow!
By the way: I haven’t access to any Excel currently. Can somebody tell me if recent versions still show the wrong result in the given case?
Yep! Excel 2019 (installed app) and Microsoft 365 Excel (cloud based) both return 0,09=1/11 when formatted for fractions with two digits.
Calc - both LibreOffice and OpenOffice - returns 8/89.
I only know a bit about the theory of continued fractions mostly used to find best common fractions approximating a real target. Currently “simple” continued fractions are also used for fractional formatting in Calc. Following the German literature there is the distinction between approximating fractions directly obtained by continued fractional decomposition and others being “best” concerning a limit set for the denominator: “Hauptnäherungsbruch” vs. “Nebennäherungsbruch”. (I cannot find the English terms.) Also the second kind can be found based on simple continued fractions, but the process needs additional steps. This is done for Calc. Excel may probably use simple continued fractions without taking in account the second variant. 1/11 e.g. is simply obtained with one step disregarding the “satellites”.
Can somebody see more clearly if Excel actually sticks to “primary” approximations?
Different approach:
How does it show Pi() with the format code # ?/?????
I also tested in Google sheets and WPS Spreadsheets. Both return the 8/89 approximation.
hello @Lupp, old ex$el ver. (2010) available to me doesn’t have that format as option, ‘up to three digits’ shows pi() as ‘3 16/113’,
i wouldn’t throw too much stones on M$, calc’s fractions have been worse up to shortly, and my calc calculates the difference between 1/11 and 0,09 being 1,12359550561798 times that between 8/89 and 0,09, not 8-fold,
@OP: ‘always the same fraction value regardless of whether it’s opened in LOC or MSE?’
… calc’s fractions have been worse up to shortly,
Not sure what you mean by that. For this example, all LO versions I have tested, and even the predecessor OpenOffice, version 2.4.3 (12 years back) render 0,09 as 8/89.
… my calc calculates the difference between 1/11 and 0,09 being 1,12359550561798 times that between 8/89 and 0,09, not 8-fold.
0,090909090909091, difference 0,000909090909091 (~1% high)0,089887640449438, difference -0,000112359550562 (~0,1% low)Check your calculation again.
0,000909090909091 / -0,000112359550562 = -8,090909090909200 which looks a lot like @Lupp’s result.
…inverted…
-0,000112359550562 / 0,000909090909091 = -0,123595505617976 which looks a lot like your result (same but not same…).
Quoting @newbie-02 : “…calc’s fractions have been worse up to shortly…”
What’s meant by “shortly”? Please name versions in a cases like this one.
Quoting @newbie-02 : " …and my calc calculates the difference between 1/11 and 0,09 being 1,12359550561798 times that between 8/89 and 0,09, not 8-fold,"
Look into the attachment ,errorByFractionFormatRevalued.ods and tell me in what way it’s wrong, please.
Also: tdf#99996
Since then there only was (afair) one serious bug concerning fractional formats caused by treating the integer part with an integer type (Long) without catching the expectable error for INT(ABS(value))>MaxLong. The bug was fixed by showing the error #FMT in these cases. See tdf#137453 .
Reworked example: errorByFractionFormatRevalued_2.ods
@Lupp and @keme:
don’t get so excited right away, i make mistakes, you (rarely) make mistakes, calc makes mistakes, ex$el makes mistakes …
this: Calc funny result with cell formatted as 'fraction' / tdf#137453 was worse than the deviation in ex$el - imho -,
“8-fold” sorry, me bad, might have been a typo induced by the typo in the first comment, can’t recheck, closed without saving 
but - to compare ex$el accuracy with calc: ‘=99999999,9999999 - 99999999,9999997’ ex$el: some value with deviation, calc: ‘0’, that’s a relation of one to millions 
wouldn’t be bad if we could teach calc to do fractional arithmetik, that could be precise in IEEE’s too? (123412/100 - 1234/1 calculated fractional as 123412/100 - 123400/100 would not result in 0,11999999999989100000?)
Who got excited?
Did you study the mentioned bug reports?
You won’t -nor will anybody else- teach Calc “fractional arithmetic”. Most input never was an exact rational number, any number given in a different way has a not exactly known deviation, and the conversion to a fraction would be arbitrary in a sense. Where exact rational arithmetiik is acpplicable at all, it can easily overplay the resources concerning RAM and time.
Use a real math program if you want to do “fractional arithmetic”. There are some, even free. (The one I sometimes use is wxMaxima with GNUplot.)
@Lupp,
sorry that I’m still trying to improve calc, I just hold up the idea of free software, and to transfer all my projects to another system I lack as much time as to learn another system or even to choose which one,
calc grows with me just kinda like other people ex$el, you started once, so you bite through the problems,
and if the programs / the community play along … a little more is certainly possible than realized so far,
I have entered somewhere else if you can help to find a formula to decide: bin-ULP <= dec-ULP or not, with this I could continue to work, but at the moment I am stuck on the fact that calc for the integer of the logarithm a few ULPs far down - ~ 10 ~ still delivers the one of the next range …
to widen my scope i just tried fractional math in calc, A1 enumerator, A2 denominator, B1 enumerator, B2 denominator, the formulas become somewhat complex, but instant correct results … not practical, but if somebody would do it in code … ???
Well, I simply think you won’t be able to improve Calc the way you try.
Did you study the bug-thread concerning improvements to fractional formatting I linked to? If so you may have stated that I also look for improvements…
hello @Lupp,
'you won’t be able to improve Calc the way you try. ’ - never say never, perhaps i’ll not improve calc but IEEE ??? 
and … (oh, I don’t want to sink into self-praise here, but) I could already initiate a few small improvements …
‘Did you study the bug-thread concerning improvements to fractional formatting I linked to?’ - not extensively as ‘fractions’ is not my main path,
‘If so you may have stated that I also look for improvements…’ - have seen that often and its one of the things i like very well …
@Lupp: sometimes i’m quite slow, sorry … ‘Why should somebody want to get the MS-Excel result despite the fact that it’s absolute error is more than 8 times as large as the error produced by the LibO Calc result?’ - may be he needs exchanging data with ex$el users, wants similar results, and can’t change ex$el … ‘compatibility’?, i’m not a fan of such but prefer accurate math, but others are other …
Well, my “Question” was rhetorical, of course, and I could imagine a reason of the kind you describe now.
I would suggest, however, to use Excel in such a case, and not to expect the better software to produce the results of the worse one. (We had many “questions” of the kind - or even nonsense posts containing insults.)